Re: Curious index selection when using a date range

Scott Marlowe skrev 2010-01-03 22.03:
> On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö<robert@blogg.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.

/r