From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Murat Tasan <mmuurr@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: does the query planner consider work_mem?
Date: Fri, 01 Jun 2012 10:59:17 +1200
Message-ID: <4FC7F7C5.2020108@catalyst.net.nz> (raw)
In-Reply-To: <CA+YV+HxVLPGYRaN9HEfH7UDUDP40g4a=dKRC-NZF+i_a8oVGVg@mail.gmail.com>
References: <CA+YV+HxVLPGYRaN9HEfH7UDUDP40g4a=dKRC-NZF+i_a8oVGVg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, mmuurr@gmail.com
Subject: Re: does the query planner consider work_mem?
In-Reply-To: <4FC7F7C5.2020108@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox