Message-ID: <4FC7F7C5.2020108@catalyst.net.nz>
Date: Fri, 01 Jun 2012 10:59:17 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: Murat Tasan <mmuurr@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: does the query planner consider work_mem?
References: 
 <CA+YV+HxVLPGYRaN9HEfH7UDUDP40g4a=dKRC-NZF+i_a8oVGVg@mail.gmail.com>
In-Reply-To: 
 <CA+YV+HxVLPGYRaN9HEfH7UDUDP40g4a=dKRC-NZF+i_a8oVGVg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 31/05/12 05:57, Murat Tasan wrote:
> hi all - i'm having a bit of trouble with some queries that are
> running painfully slowly after migrating my database from one machine
> using PostgreSQL 8.2 to another machine with PostgreSQL 8.4.
> as far as i can tell, the two *servers* (not the physical machines)
> are set up pretty close to identically, and as far as query planning
> is concerned, the only setting that seems to be different is
> 'default_statistics_target', which is 10 on the 8.2 sever and 100 on
> the 8.4 server (the defaults)... and presumably this should be giving
> the 8.4 server more data to work with for better query plans (AFAIK).
> (other settings, e.g. cost estimates for page/row/etc access are
> identical between the servers.)

It would probably be useful know what release of 8.4 you have - i.e 
8.4.x. There were some significant planner changes at 8.4.9 or thereabouts.

I think it would also be useful to know all of your non default 
parameters for 8.4 (SELECT name,setting FROM pg_settings WHERE source != 
'default').

> 3) here's the biggest problem/issue in my brain: work_mem on the 8.2
> server was also set to the 1 MB default! but ran quite speedily!
> the full migration will take a while, so there will still be query
> development/optimization on one system, and i'd love for those many
> hours testing to be worth something when ported over to the other
> system.
> in this particular example, the Nested Loop seems to fit in the 1 MB
> work_mem space on the 8.2 server, but not the 8.4? does this seem
> right to anybody?
>
>
>

Well 8.4 has 100 stats buckets to get distribution info, so typically 
has a better idea about things, however sometimes more info is just 
enough to tip the planner into believing that it needs more space to do 
something. The other possibility is that the 8.2 box is 32-bit and the 
8.4 one is 64-bit and really does need more memory to hold the loop data 
structures.

Regards

Mark