Re: Slow query - possible bug? - Mailing list pgsql-performance

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.