Re: Curious index selection when using a date range

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?