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.
/r
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
