Re: Range contains element filter not using index of the element column

> On 27 Nov 2019, at 10:32, Lauri Kajan <[email protected]> wrote:
> 
> Hi all,
> I'm wondering if there are anything to do to utilize a index when doing a range contains element  query. I have tested this with 9.6 and 12.0.
> 
> I have a table with a timestamp column that has a btree index.
> I would like to do a query:
> SELECT * FROM table WHERE ts <@ tsrange($1, $2, '(]');
> The index is not used and a seq scan is done instead.
> 
> To use the index correctly I have to do the query like this:
> SELECT * FROM table WHERE  ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <= $2);
> I like the <@ syntax more. Is there something I can do differently? Maybe a different type of index instead?

Does it help to use timestamps -infinity and infinity instead of nulls in your case?

=> select t, t < current_timestamp, current_timestamp <= t from (values ('-infinity'::timestamp), ('infinity'::timestamp)) x(t);                           
     t     | ?column? | ?column? 
-----------+----------+----------
 -infinity | t        | f
 infinity  | f        | t
(2 rows)

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.