Re: Slow query - possible bug?

Gavin Hamill <gdh(at)laterooms(dot)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