Really poor gist index performance with tstzrange types

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Really poor gist index performance with tstzrange types
Date: 2013-06-11 23:17:35
Message-ID: CACfv+pLkpPLEt03OFKysjy22CRVqBaj90MaHGOtMnQRn+oDL+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Am I doing something silly? Or is the row-estimation for gist indexes not
even close in this case?

https://gist.github.com/joevandyk/503cc3d836ee5d101224/raw/c6fc53b2da06849d3d04effbd1c147fc36124245/gistfile1.txtor
code below:

-- This is not running inside a transaction.

drop table if exists f;

create table f (duration tstzrange);

insert into f
select tstzrange(now() - '1 month'::interval, now() - '1
sec'::interval) from generate_series(1, 100000);

create index on f using gist(duration);
analyze f;

select count(*) from f where tstzrange(now(), now(), '[]') << duration;
-- returns 0

explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') << duration;

Aggregate (cost=2720.36..2720.37 rows=1 width=0) (actual
time=55.374..55.374 rows=1 loops=1)
-> Seq Scan on f (cost=0.00..2637.02 rows=33334 width=0) (actual
time=55.369..55.369 rows=0 loops=1)
Filter: (tstzrange(now(), now(), '[]'::text) << duration)
Rows Removed by Filter: 100000
Total runtime: 55.407 ms

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-06-12 00:13:54 Re: Really poor gist index performance with tstzrange types
Previous Message Don Parris 2013-06-11 21:35:19 PostgreSQL Presentation at SELF 2013