Tom Lane wrote:
Gavin Hamill <gdh@xxxxxxxxxxxxx> 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.
