Curious index selection when using a date range
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
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?
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
On Sun, Jan 3, 2010 at 2:16 PM, Robert Gravsjö <robert@blogg.se> wrote:
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.
You might need to crank up the aggresiveness of auto-analyze, at least
on that one table if not for the whole db.
Scott Marlowe <scott.marlowe@gmail.com> writes:
You might need to crank up the aggresiveness of auto-analyze, at least
on that one table if not for the whole db.
This might be the same type of scenario Josh was complaining about:
http://archives.postgresql.org/pgsql-hackers/2009-12/msg02308.php
Inequality conditions near the end of a moving data range may not
get estimated very accurately, unless you analyze very often.
I'm fooling with a patch for that right now.
regards, tom lane