27.11.2019, 16:32, "Lauri Kajan" <lauri.kajan@xxxxxxxxx>: > 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 &>
