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

Joe Conway <[email protected]> Wed, 27 Nov 2019 08:36:17 -0500

Newsgroups	gmane.comp.db.postgresql.general
Message-ID	<[email protected]>

On 11/27/19 6:33 AM, Lauri Kajan wrote:
> On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <[email protected]
> <mailto:[email protected]>> 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 (application/pgp-signature, 833 B)