2009/11/20 Richard Neill <rn...@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.
>>
>
> Yes, I see what you mean.
>
>
>
>> Have you manually run a VACUUM on these tables? Preferrably a full one if
>> you can.
>>
>
> Every night, it runs Vacuum verbose analyze on the entire database. We also
> have the autovacuum daemon enabled (in the default config).
>
> 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?
>>
>
> I think so. I didn't actually write all of this, so I can't be certain.
>
>
> Also consider
>
>> using partial or multicolumn indexes where useful.
>>
>>
> 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.
>
>
> And which version of PostgreSQL are you using?
>>
>
> 8.4.1, including this patch:
> 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
