Postgres Pro
Downloads
Home   >   mailing lists

Thread: Really poor gist index performance with tstzrange types

Really poor gist index performance with tstzrange types

From
Joe Van Dyk
Date:
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: 100000Total runtime: 55.407 ms

Re: Really poor gist index performance with tstzrange types

From
Tom Lane
Date:
Joe Van Dyk <joe@tanga.com> writes:
> Am I doing something silly? Or is the row-estimation for gist indexes not
> even close in this case?

9.2 didn't have any logic for estimating range << conditions.  I see
reasonable estimates for this case in HEAD, though, presumably thanks
to work by Alexander Korotkov.

            regards, tom lane

Re: Really poor gist index performance with tstzrange types

From
Sergey Konoplev
Date:
On Tue, Jun 11, 2013 at 5:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joe Van Dyk <joe@tanga.com> writes:
>> Am I doing something silly? Or is the row-estimation for gist indexes not
>> even close in this case?
>
> 9.2 didn't have any logic for estimating range << conditions.  I see
> reasonable estimates for this case in HEAD, though, presumably thanks
> to work by Alexander Korotkov.

I just wanted to add that rewriting the << condition the way shown
below might partially solve the problem.

where
    tstzrange(now(), now(), '[]') < duration and
    not tstzrange(now(), now(), '[]') && duration

And here is the result.

[local]:5432 grayhemp@grayhemp=#
explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') << duration;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2720.17..2720.18 rows=1 width=0) (actual
time=109.161..109.163 rows=1 loops=1)
   ->  Seq Scan on f  (cost=0.00..2636.84 rows=33331 width=0) (actual
time=109.148..109.148 rows=0 loops=1)
         Filter: (tstzrange(now(), now(), '[]'::text) << duration)
         Rows Removed by Filter: 100000
 Total runtime: 109.210 ms
(5 rows)

Time: 109.837 ms
[local]:5432 grayhemp@grayhemp=#
explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') < duration
and not tstzrange(now(), now(), '[]') && duration;
                                                             QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2646.39..2646.40 rows=1 width=0) (actual
time=0.042..0.043 rows=1 loops=1)
   ->  Bitmap Heap Scan on f  (cost=926.55..2563.48 rows=33164
width=0) (actual time=0.035..0.035 rows=0 loops=1)
         Recheck Cond: (tstzrange(now(), now(), '[]'::text) < duration)
         Filter: (NOT (tstzrange(now(), now(), '[]'::text) && duration))
         ->  Bitmap Index Scan on f_duration_idx1  (cost=0.00..918.26
rows=33331 width=0) (actual time=0.030..0.030 rows=0 loops=1)
               Index Cond: (tstzrange(now(), now(), '[]'::text) < duration)
 Total runtime: 0.098 ms
(7 rows)

Time: 0.801 ms

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com