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
