From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Joe Van Dyk <joe(at)tanga(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Really poor gist index performance with tstzrange types |
Date: | 2013-06-12 02:13:59 |
Message-ID: | CAL_0b1tN5eEpWhwBKSBtgozyLun3DaSQXhPHCSqjKih+PBCtSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jun 11, 2013 at 5:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joe Van Dyk <joe(at)tanga(dot)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(at)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(at)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(dot)ru(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Inoue, Hiroshi | 2013-06-12 03:25:53 | Re: Segmentation fault with core dump |
Previous Message | Tom Lane | 2013-06-12 00:13:54 | Re: Really poor gist index performance with tstzrange types |