27.11.2019, 16:32, "Lauri Kajan" <lauri.ka...@gmail.com>:
> 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?
>
> Thanks,
>
> -Lauri
Hi!
Do you use GIST index?
According to
https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING <@
operator is supported:
> A GiST or SP-GiST index can accelerate queries involving these range
> operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>
