On Sun, Jan 3, 2010 at 2:16 PM, Robert Gravsjö <robert@xxxxxxxx> wrote: > Scott Marlowe skrev 2010-01-03 22.03: >> On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö<robert@xxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
