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: 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?


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to