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

Re: Difference in query plan

From: Patrice Beliveau <pbeliveau(at)avior(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Difference in query plan
Date: 2008-11-14 17:07:45
Message-ID: 491DB061.4020002@avior.ca (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)postgresql(dot)org) To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-performance .


Attachment: pbeliveau.vcf
Description: text/x-vcard (403 bytes)

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2008-11-14 17:14:17
Subject: Re: Difference in query plan
Previous:From: Richard HuxtonDate: 2008-11-14 16:47:41
Subject: Re: Difference in query plan

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