Search Postgresql Archives

Really poor gist index performance with tstzrange types

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.txt or 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux