Обсуждение: 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