Skip site navigation (1) Skip section navigation (2)

Re: Postgres query completion status?

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres query completion status?
Date: 2009-11-20 19:39:53
Message-ID: bddc86150911201139s65424488je3ebda276f47ca6e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2009/11/20 Richard Neill <rn214(at)cam(dot)ac(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Richard NeillDate: 2009-11-20 20:14:15
Subject: Re: Postgres query completion status?
Previous:From: Richard NeillDate: 2009-11-20 19:16:54
Subject: Re: Postgres query completion status?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group