On 11/27/19 6:33 AM, Lauri Kajan wrote: > On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко <vyskorko.igor@xxxxxxxxx > <mailto:vyskorko.igor@xxxxxxxxx>> 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
Attachment:
signature.asc
Description: OpenPGP digital signature
