From: Gavin Hamill <gdh@laterooms.com>
To: pgsql-performance@postgresql.org
Subject: Slow query - possible bug?
Date: Thu, 13 Apr 2006 13:03:01 +0100
Message-ID: <443E3DF5.4020207@laterooms.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdh@laterooms.com
Subject: Re: Slow query - possible bug?
In-Reply-To: <443E3DF5.4020207@laterooms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox