Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size.
shared_buffers: 13384
effective_cache_size: 4000
server memory: 2GB
Please note, the databases are on the same server, it's merely 2 instances of the same database in order to figure out why there's a difference in the query plan before and after a dump / restore.
What worries me is that the plan is different, in the bad plan it makes a seq scan of a table with 6.5k recods in (fairly silly) and another of a table with 50k records in (plan stupid).
In the good plan it uses the indexes available as expected.
The estimated cost is obviously way off in the live database, even though statistics etc should be up to date. Any insight into this?
Appreciate the help here...
Cheers
Jona
Dennis Bjorklund wrote:
On Thu, 9 Jun 2005 jonanews@oismail.com wrote:
I am continously encountering an issue with query plans that changes after
a pg_dump / pg_restore operation has been performed.
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.
There is not a major difference in time, so pg is at least not way off
(225ms vs. 280ms). The estimated cost is however not very related to the
runtime (117 vs 1389).
What you have not showed is if the database is properly tuned. The output
of SHOW ALL; could help explain a lot together with info of how much
memory your computer have.
The first thing that comes to mind to me is that you probably have not
tuned shared_buffers and effective_cache_size properly (SHOW ALL would
tell).
