Range contains element filter not using index of the element column

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Range contains element filter not using index of the element column

Lauri Kajan
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

Reply | Threaded
Open this post in threaded view
|

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

Игорь Выскорко


27.11.2019, 16:32, "Lauri Kajan" <[hidden email]>:

> 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 &>


Reply | Threaded
Open this post in threaded view
|

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

Lauri Kajan
On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <[hidden email]> 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.


-Lauri 
Reply | Threaded
Open this post in threaded view
|

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

Joe Conway
On 11/27/19 6:33 AM, Lauri Kajan wrote:

> On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <[hidden email]
> <mailto:[hidden email]>> 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


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Lauri Kajan
Lauri Kajan <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Lauri Kajan
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

Reply | Threaded
Open this post in threaded view
|

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

Alban Hertroys-4
In reply to this post by Lauri Kajan

> On 27 Nov 2019, at 10:32, Lauri Kajan <[hidden email]> 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.