Re: Curious index selection when using a date range

On Sun, Jan 3, 2010 at 2:16 PM, Robert Gravsjö <robert(at)blogg(dot)se> wrote:
> Scott Marlowe skrev 2010-01-03 22.03:
>> On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö<robert(at)blogg(dot)se>  wrote:
>>>
>>> I encountered a curious thing today. Simple select queries against a
>>> fairly
>>> large, ~60M rows, and active, both in reading and writing, suddenly were
>>> aweful slow, from milliseconds into 10th of seconds.
>>>
>>> Looking a bit closer revealed that on a date condition having a between
>>> 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was
>>> choosen while if the year was switched to 2009 a composed index making
>>> use
>>> of the other condition parameters as well was choosen.
>>>
>>> After this we ran vacuum analyze on the table which solved the issue with
>>> the composed index getting used for the current year as well.
>>
>> Assuming the analyze part is what fixed this, then the problem is
>> you're analyzing often enough.  Got autovac on?  What version of pgsql
>> are you running?
>
> We're using autovaccum and running PostgreSQL 8.4.1, compiled with GCC
> 4.3.4, on Linux kernel 2.6.31 on x86_64 arch.

You might need to crank up the aggresiveness of auto-analyze, at least
on that one table if not for the whole db.