2009/11/20 Richard Neill <rn214@xxxxxxxxx>
Yes, I see what you mean.
Thom Brown wrote:
>
It looks like your statistics are way out of sync with the real data.
> Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual time=248577.879..253168.466 rows=347308 loops=1)
This shows that it thinks there will be 8,686 rows, but actually traverses 347,308.
Every night, it runs Vacuum verbose analyze on the entire database. We also have the autovacuum daemon enabled (in the default config).
Have you manually run a VACUUM on these tables? Preferrably a full one if you can.
About 2 weeks ago, I ran cluster followed by vacuum full - which seemed to help more than I'd expect.
[As I understand it, the statistics shouldn't change very much from day to day, as long as the database workload remains roughly constant. What we're actually doing is running a warehouse sorting books - so from one day to the next the particular book changes, but the overall statistics basically don't.]I think so. I didn't actually write all of this, so I can't be certain.
I notice that you appear ot have multiple sorts going on.
Are all of those actually necessary for your output?
Already done that. The query was originally pretty quick, with a few weeks worth of data, but not now. (after a few months). The times don't rise gradually, but have a very sudden knee.
Also consider
using partial or multicolumn indexes where useful.
8.4.1, including this patch:
And which version of PostgreSQL are you using?
http://archives.postgresql.org/pgsql-bugs/2009-10/msg00118.php
Richard
Okay, have you tried monitoring the connections to your database?
Try: select * from pg_stat_activity;
And this to see current backend connections:
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
It might also help if you posted your postgresql.conf too.
Thom
