pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jonanews@oismail.com
To: pgsql-performance@postgresql.org
Subject: Query plan changes after pg_dump / pg_restore
Date: Thu, 9 Jun 2005 02:02:02 -0400 (EDT)
Message-ID: <Pine.LNX.4.61.0506090200150.8506@oismail.com> (raw)

Greetings all,
I am continously encountering an issue with query plans that changes after 
a pg_dump / pg_restore operation has been performed.
On the production database, PostGre refuses to use the defined indexes in 
several queries however once the database has been dumped and restored 
either on another server or on the same database server it suddenly 
"magically" changes the query plan to utilize the indexes thereby cutting 
the query cost down to 10% of the original.
Databases are running on the same PostGre v7.3.9 on RH Enterprise 3.1 
server.

A VACUUM FULL runs regularly once a day and VACUUM ANALYZE every other 
hour.
The data in the tables affected by this query doesn't change very often
Even doing a manual VACUUM FULL, VACUUM ANALYZE or REINDEX before the 
query is run on the production database changes nothing.
Have tried to drop the indexes completely and re-create them as well, all 
to no avail.

If the queries are run with SET ENABLE_SEQSCAN TO OFF, the live database 
uses the correct indexes as expected.

Have placed an export of the query, query plan etc. online at: 
http://213.173.234.215:8080/plan.htm in order to ensure it's still 
readable.
For the plans, the key tables are marked with bold.

Any insight into why PostGre behaves this way as well as a possible 
solution (other than performing a pg_dump / pg_restore on the live 
database) would be very much appreciated?

Cheers
Jona



view thread (11+ messages)  latest in thread

Message-ID: <Pine.LNX.4.61.0506090200150.8506@oismail.com>
Permalink:  ../Pine.LNX.4.61.0506090200150.8506@oismail.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.61.0506090200150.8506@oismail.com

 · 

reply

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: jonanews@oismail.com
  Subject: Re: Query plan changes after pg_dump / pg_restore
  In-Reply-To: <Pine.LNX.4.61.0506090200150.8506@oismail.com>

* 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