Curious index selection when using a date range

From: Robert Gravsjö <robert(at)blogg(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Curious index selection when using a date range
Date: 2010-01-03 20:10:27
Message-ID: 4B40F9B3.8050203@blogg.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Does anyone of you guys have a good explanation for this behaviour? I'm
only guessing about why the planner made this choice.

I'm well aware of not supplying enough details about our scenario but
I'm disconnected at the moment and I'm asking just out of curiousity
(and hope I wont meet the same faith as the cat did).

Sincerly,
roppert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-01-03 21:03:32 Re: Curious index selection when using a date range
Previous Message Greg Stark 2010-01-03 18:58:11 Re: WEIRD! postmaster: segfault with sub select??!