Обсуждение: Range contains element filter not using index of the element column
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
27.11.2019, 16:32, "Lauri Kajan" <lauri.kajan@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 testedthis 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 &>
On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <vyskorko.igor@yandex.ru> wrote:
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 &>
I have understood that gist indexes can be used if the column is range type but my column is just plain timestamp.
I tried actually to add gist index for the timestamp column. That was not possible without installing the btree_gist extension. But that didn't work.
-Lauri
On 11/27/19 6:33 AM, Lauri Kajan wrote: > On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <vyskorko.igor@yandex.ru > <mailto:vyskorko.igor@yandex.ru>> wrote: > > 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 &> > > > Hi, > > I have understood that gist indexes can be used if the column is range > type but my column is just plain timestamp. > I tried actually to add gist index for the timestamp column. That was > not possible without installing the btree_gist extension. But that > didn't work. Try this: create table tstest(id int, ts timestamptz); insert into tstest select g.i, now() - (g.i::text || ' days')::interval from generate_series(1, 100000) as g(i); create index tstest_gin on tstest using gist((tstzrange(ts,ts,'[]'))); explain analyze select * from tstest where tstzrange(ts,ts,'[]') <@ tstzrange(now()- '9 days'::interval, now()-'7 days'::interval,'(]'); QUERY PLAN -------------------------------------------------------- Bitmap Heap Scan on tstest (cost=24.17..590.16 rows=500 width=12) (actual time=0.069..0.070 rows=2 loops=1) Recheck Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now() - '9 days'::interval), (now() - '7 days'::interval), '(]'::text)) Heap Blocks: exact=1 -> Bitmap Index Scan on tstest_gin (cost=0.00..24.04 rows=500 width=0) (actual time=0.063..0.063 rows=2 loops=1) Index Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now() - '9 days'::interval), (now() - '7 days'::interval), '(]'::text)) Planning Time: 20.920 ms Execution Time: 0.115 ms (7 rows) HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Вложения
Lauri Kajan <lauri.kajan@gmail.com> writes: > 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? As others mentioned, a gist index on a tsrange expression could be used for this, but another idea is to build some syntactic sugar using a custom operator. Light testing suggests that this works: create function expand_range_contain(anyelement, anyrange) returns bool language sql parallel safe as $$ select case when lower_inf($2) then true when lower_inc($2) then $1 >= lower($2) else $1 > lower($2) end and case when upper_inf($2) then true when upper_inc($2) then $1 <= upper($2) else $1 < upper($2) end $$; create operator <<@ ( function = expand_range_contain, leftarg = anyelement, rightarg = anyrange ); select * from table where ts <<@ tsrange($1, $2, '(]'); An important caveat though is that the range operand *must* reduce to a constant. If the planner fails to const-simplify those CASE expressions, you'll not only not get an indexscan, but you'll be worse off than with the native <@ operator. So this isn't an all-purpose fix --- but it might cover your needs and be nicer than maintaining a second index on the column. regards, tom lane
Thank you Jon and Tom!
Both of those ideas seem to work.
Do you think this is worth of a feature request? Would there be any use if btree index is used in these certain situations directly with @>?
Thanks,
Lauri
> On 27 Nov 2019, at 10:32, Lauri Kajan <lauri.kajan@gmail.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 testedthis 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.