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
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??! |