From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gavin Hamill <gdh@laterooms.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query - possible bug?
Date: Tue, 18 Apr 2006 13:31:48 -0400
Message-ID: <14985.1145381508@sss.pgh.pa.us> (raw)
In-Reply-To: <44449E4C.7040804@laterooms.com>
References: <443E3DF5.4020207@laterooms.com>
<3c1395330604130546g6b091f75x511955817fe65dcd@mail.gmail.com>
<443E4C9D.3090301@laterooms.com>
<20329.1144942010@sss.pgh.pa.us>
<44449E4C.7040804@laterooms.com>
Gavin Hamill <gdh@laterooms.com> writes:
> Tom Lane wrote:
>> I'm thinking the planner is misestimating something, but it's hard
>> to tell what without breaking it down.
> (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
Bingo, there's our misestimation: estimated 1 row, actual 34220 :-(
That's why it's choosing the wrong index: it thinks the condition on
RoomID isn't going to reduce the number of rows fetched any further,
and so the smaller index ought to be marginally cheaper to use.
In reality, it works way better when using the two-column index.
I think this is the same problem recently discussed about how the
degenerate case for a range comparison is making an unreasonably small
estimate, where it probably ought to fall back to some equality estimate
instead. With the simple-equality form of the date condition, it does
get a reasonable estimate, and so it picks the right index.
There should be a fix for this by the time PG 8.2 comes out, but in the
meantime you might find that it helps to write the range check in a way
that doesn't have identical bounds, eg
date >= '2006-06-10'::date AND date < '2006-06-11'::date
regards, tom lane
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: tgl@sss.pgh.pa.us, gdh@laterooms.com
Subject: Re: Slow query - possible bug?
In-Reply-To: <14985.1145381508@sss.pgh.pa.us>
* 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