Thank you for the swift reply, the following is the output of the SHOW
ALL for shared_buffers and effective_cache_size.|
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...
Dennis Bjorklund wrote:
On Thu, 9 Jun 2005 [EMAIL PROTECTED] 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://22.214.171.124: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).
- [PERFORM] Query plan changes after pg_dump / pg_re... jonanews
- Re: [PERFORM] Query plan changes after pg_dum... Dennis Bjorklund
- Re: [PERFORM] Query plan changes after pg... Jona
- Re: [PERFORM] Query plan changes afte... Christopher Kings-Lynne
- Re: [PERFORM] Query plan changes afte... Tom Lane