Re: does the query planner consider work_mem?

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