Re: Query plan changes after pg_dump / pg_restore

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(at)oismail(dot)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).
>
>
>