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