Message-ID: <443E3DF5.4020207@laterooms.com>
Date: Thu, 13 Apr 2006 13:03:01 +0100
From: Gavin Hamill <gdh@laterooms.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow query - possible bug?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

laterooms=# explain analyze 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 
(allocation0_."Date" between '2006-06-09 00:00:00.000000' and 
'2006-06-09 00:00:00.000000')and(allocation0_."RoomID" in(4300591));
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ix_date on "Allocation" allocation0_  (cost=0.00..4.77 
rows=1 width=34) (actual time=1411.325..1689.860 rows=1 loops=1)
   Index Cond: (("Date" >= '2006-06-09'::date) AND ("Date" <= 
'2006-06-09'::date))
   Filter: ("RoomID" = 4300591)
 Total runtime: 1689.917 ms
(4 rows)

Yep, the two dates are identical - yep I would change the client 
software to do where "Date" = '2006-06-09 00:00:00.000000' if I could...

However, it's clear to see why this simple query is taking so long - the 
plan is selecting /all/ dates after 2006-06-09 and /all/ dates before 
then, and only returning the union of the two - a large waste of effort, 
surely?

VACUUM ANALYZE hasn't improved matters... the schema for the table is

 "ID" int8 NOT NULL DEFAULT 
nextval(('public."allocation_id_seq"'::text)::regclass),
  "RoomID" int4,
  "Price" numeric(10,2),
  "StatusID" int4,
  "Number" int4,
  "Date" date,

and there are indexes kept for 'RoomID' and 'Date' in this 4.3-million 
row table.

Is this a bug or a hidden feature in pg 8.1.3 ? :)

Cheers,
Gavin.