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.