From: Gavin Hamill <gdh@laterooms.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query - possible bug?
Date: Tue, 18 Apr 2006 09:07:40 +0100
Message-ID: <44449E4C.7040804@laterooms.com> (raw)
In-Reply-To: <20329.1144942010@sss.pgh.pa.us>
References: <443E3DF5.4020207@laterooms.com>
<3c1395330604130546g6b091f75x511955817fe65dcd@mail.gmail.com>
<443E4C9D.3090301@laterooms.com>
<20329.1144942010@sss.pgh.pa.us>
Tom Lane wrote:
>Gavin Hamill <gdh@laterooms.com> writes:
>
>
>>If I replace the
>>(allocation0_."Date" between '2006-06-09 00:00:00.000000' and
>>'2006-06-09 00:00:00.000000')
>>with
>>allocation0_."Date" ='2006-04-09 00:00:00.000000'
>>then the query comes back in a few milliseconds (as I'd expect :)
>>
>>
>
>Could we see EXPLAIN ANALYZE for
>* both forms of the date condition, with the roomid condition;
>* both forms of the date condition, WITHOUT the roomid condition;
>* just the roomid condition
>
>I'm thinking the planner is misestimating something, but it's hard
>to tell what without breaking it down.
>
>
Of course. In each case, I have changed the date by two weeks to try and
minimise the effect of any query caching.
The base query is "explain analyse select allocation0_."ID" as y1_,
allocation0_."RoomID" as y2_, allocation0_."StatusID" as y4_,
allocation0_."Price" as y3_, allocation0_."Number" as y5_,
allocation0_."Date" as y6_ from "Allocation" allocation0_ where"
now both forms of the Date condition
a)
(allocation0_."Date" between '2006-04-25 00:00:00.000000' and
'2006-04-25 00:00:00.000000')and(allocation0_."RoomID" in(211800));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_date on "Allocation" allocation0_ (cost=0.00..4.77
rows=1 width=34) (actual time=3253.340..48040.396 rows=1 loops=1)
Index Cond: (("Date" >= '2006-04-25'::date) AND ("Date" <=
'2006-04-25'::date))
Filter: ("RoomID" = 211800)
Total runtime: 48040.451 ms (ouch!)
b)
(allocation0_."Date"= '2006-05-10
00:00:00.000000'::date)and(allocation0_."RoomID" in(211800));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_dateroom on "Allocation" allocation0_
(cost=0.00..5.01 rows=1 width=34) (actual time=0.033..0.035 rows=1 loops=1)
Index Cond: (("RoomID" = 211800) AND ("Date" = '2006-05-10'::date))
Total runtime: 0.075 ms (whoosh!)
And now without the RoomID condition:
a)
(allocation0_."Date" between '2006-06-10 00:00:00.000000' and
'2006-06-10 00:00:00.000000');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_date on "Allocation" allocation0_ (cost=0.00..4.77
rows=1 width=34) (actual time=0.035..6706.467 rows=34220 loops=1)
Index Cond: (("Date" >= '2006-06-10'::date) AND ("Date" <=
'2006-06-10'::date))
Total runtime: 6728.743 ms
b)
(allocation0_."Date"= '2006-05-25 00:00:00.000000'::date);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on "Allocation" allocation0_ (cost=87.46..25017.67
rows=13845 width=34) (actual time=207.674..9702.656 rows=34241 loops=1)
Recheck Cond: ("Date" = '2006-05-25'::date)
-> Bitmap Index Scan on ix_date (cost=0.00..87.46 rows=13845
width=0) (actual time=185.086..185.086 rows=42705 loops=1)
Index Cond: ("Date" = '2006-05-25'::date)
Total runtime: 9725.470 ms
Wow, I'm not really sure what that tells me...
Cheers,
Gavin.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdh@laterooms.com
Subject: Re: Slow query - possible bug?
In-Reply-To: <44449E4C.7040804@laterooms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox