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

> On 27 Nov 2019, at 10:32, Lauri Kajan <lauri(dot)kajan(at)gmail(dot)com> 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.