Re: Postgres query completion status?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2009/11/20 Richard Neill <rn214@xxxxxxxxx>


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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux