Re: Slow query - possible bug?

Tom Lane wrote:

>Gavin Hamill <gdh(at)laterooms(dot)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.