From: | Richard Neill <rn214(at)cam(dot)ac(dot)uk> |
---|---|
To: | |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Postgres query completion status? |
Date: | 2009-11-20 19:16:54 |
Message-ID: | 4B06EB26.3030608@cam.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2009-11-20 19:39:53 | Re: Postgres query completion status? |
Previous Message | Richard Neill | 2009-11-20 19:00:20 | Re: Postgres query completion status? |