2009/11/20 Richard Neill
<rn214@cam.ac.uk> 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.
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 notice that you appear ot have multiple sorts going on.
Are all of those actually necessary for your output?
Also consider
using partial or multicolumn indexes where useful.
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