X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EB581528FE
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Jun 2005 10:38:02 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 80226-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Jun 2005 13:38:00 +0000 (GMT)
Received: from oismail.com (unknown [38.117.22.100])
	by svr1.postgresql.org (Postfix) with ESMTP id 5BEBD52899
	for <pgsql-performance@postgresql.org>;
	Thu,  9 Jun 2005 10:38:00 -0300 (ADT)
Received: from [192.168.8.103] ([213.173.234.215]) (authenticated bits=0)
	by oismail.com (8.12.11/8.12.11) with ESMTP id j59DbuET019398
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 9 Jun 2005 09:37:58 -0400
Message-ID: <42A84740.4010003@oismail.com>
Date: Thu, 09 Jun 2005 15:42:24 +0200
From: Jona <jonanews@oismail.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Dennis Bjorklund <db@zigo.dhs.org>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Query plan changes after pg_dump / pg_restore
References: <Pine.LNX.4.44.0506091244490.24742-100000@zigo.dhs.org>
In-Reply-To: <Pine.LNX.4.44.0506091244490.24742-100000@zigo.dhs.org>
Content-Type: multipart/alternative;
	boundary="------------020907020505030406000309"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.115 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_30_40, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200506/212
X-Sequence-Number: 12849

This is a multi-part message in MIME format.
--------------020907020505030406000309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thank you for the insight, any suggestion as to what table / columns I 
should compare between the databases?

Cheers
Jona

Dennis Bjorklund wrote:

>On Thu, 9 Jun 2005, Jona wrote:
>
>  
>
>>It's the same (physical) server as well as the same PostGreSQL daemon, 
>>so yes.
>>    
>>
>
>The only thing that can differ then is the statistics collected and the
>amount of dead space in tables and indexes (but since you both reindex and
>run vacuum full that should not be it).
>
>So comparing the statistics in the system tables is the only thing I can 
>think of that might bring some light on the issue. Maybe someone else have 
>some ideas.
>
>And as KL said, the effective_cache_size looked like it was way to small. 
>With that setting bigger then pg should select index scans more often. It 
>doesn't explain why the databases behave like they do now, but it might 
>make pg select the same plan nevertheless.
>
>  
>


--------------020907020505030406000309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thank you for the insight, any suggestion as to what table / columns I
should compare between the databases?<br>
<br>
Cheers<br>
Jona<br>
<br>
Dennis Bjorklund wrote:
<blockquote
 cite="midPine.LNX.4.44.0506091244490.24742-100000@zigo.dhs.org"
 type="cite">
  <pre wrap="">On Thu, 9 Jun 2005, Jona wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">It's the same (physical) server as well as the same PostGreSQL daemon, 
so yes.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
The only thing that can differ then is the statistics collected and the
amount of dead space in tables and indexes (but since you both reindex and
run vacuum full that should not be it).

So comparing the statistics in the system tables is the only thing I can 
think of that might bring some light on the issue. Maybe someone else have 
some ideas.

And as KL said, the effective_cache_size looked like it was way to small. 
With that setting bigger then pg should select index scans more often. It 
doesn't explain why the databases behave like they do now, but it might 
make pg select the same plan nevertheless.

  </pre>
</blockquote>
<br>
</body>
</html>

--------------020907020505030406000309--