From: Patrice Beliveau <pbeliveau@avior.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Difference in query plan
Date: Fri, 14 Nov 2008 12:07:45 -0500
Message-ID: <491DB061.4020002@avior.ca> (raw)
Thanks,
I'm already doing a vacuum full every night on all database, but the REINDEX fix it and now it's working fine
But this raise a question
1) This table is cleared every night and recomputed, does this mean that I should REINDEX every night also
2) Why this thing didn't happen in the other schema
Thanks again
Patrice Beliveau wrote:
> > I have a database in a production server (8.1.9) with to schema
> > containing the sames table same index, same every thing, but with
> > different data. When I execute a query in one schema, it take much more
> > time to execute then the other schema.
>
[snip]
> > I'm wondering where to start searching to fix this problem
>
> > Production server schema 1 query plan:
> > Nested Loop (cost=569.23..634.43 rows=1 width=121) (actual
> > time=1032.811..1032.811 rows=0 loops=1)
>
[snip]
> > Total runtime: 1034.204 ms
>
> > Production server schema 2 query plan:
> > Nested Loop (cost=133.42..793.12 rows=1 width=123) (actual
> > time=0.130..0.130 rows=0 loops=1)
>
[snip]
> > Total runtime: 0.305 ms
>
Well there's something strange - the estimated costs are fairly similar
(643.43 vs 793.12) but the times are clearly very different (1034 vs 0.3ms)
The suspicious line from the first plan is:
> > -> Seq Scan on mrp m (cost=0.00..119.92 rows=5892
> > width=39) (actual time=0.343..939.462 rows=5892 loops=1)
>
This is taking up almost all the time in the query and yet only seems to
be scanning 5892 rows.
Run a vacuum verbose against table "mrp" and see if it's got a lot of
dead rows. If it has, run VACUUM FULL and REINDEX against it and see if
that solves your problem.
I'm guessing you have / had a long-running transaction interfering with
vacuum on this table, or perhaps a bulk update/delete?
-- Richard Huxton Archonet Ltd
-- Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance .
Attachments:pbeliveau.vcf (text/x-vcard, 401B) download
begin:vcard
fn;quoted-printable:Patrice B=C3=A9liveau
n;quoted-printable:B=C3=A9liveau;Patrice
org;quoted-printable:AVIOR Produits Int=C3=A9gr=C3=A9s Inc.
adr;quoted-printable:;;1001, autoroute 440 ouest;Laval;Qu=C3=A9bec;H7L 3W3;Canada
email;internet:pbeliveau@avior.ca
title:Directeur IT
tel;work:(450) 629-6200 #410
tel;fax:(514) 324-6241
x-mozilla-html:FALSE
url:www.avior.ca
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pbeliveau@avior.ca
Subject: Re: Difference in query plan
In-Reply-To: <491DB061.4020002@avior.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox