[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö rob...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
Scott Marlowe skrev 2010-01-03 22.03: On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjörob...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
On Sun, Jan 3, 2010 at 2:16 PM, Robert Gravsjö rob...@blogg.se wrote: Scott Marlowe skrev 2010-01-03 22.03: On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjörob...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious index selection when using a date range
Scott Marlowe scott.marl...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
