From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Hussey <peter@labkey.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Questions on query planner, join types, and work_mem
Date: Tue, 27 Jul 2010 20:05:02 -0400
Message-ID: <18551.1280275502@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
References: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
Peter Hussey <peter@labkey.com> writes:
> I have spent the last couple of weeks digging into a Postgres performance
> problem that ultimately boiled down to this: the planner was choosing to
> use hash joins on a set of join keys that were much larger than the
> configured work_mem.
What Postgres version is this, exactly? ("8.4" is not the answer I want.)
> the join column, lsid, is a poor choice for a join column as it is a long
> varchar value (avg length 101 characters) that us only gets unique way out
> on the right hand side.
Hm, but it is unique eventually? It's not necessarily bad for hashing
as long as that's so.
> 1) If we tell the customer to set his work_mem value to 500MB or 1GB in
> postgres.config, what problems might they see?
That would almost certainly be disastrous. If you have to follow the
hack-work_mem path, I'd suggest increasing it locally in the session
executing the problem query, and only for the duration of that query.
Use SET, or even SET LOCAL.
> 2) How is work_mem used by a query execution?
Well, the issue you're hitting is that the executor is dividing the
query into batches to keep the size of the in-memory hash table below
work_mem. The planner should expect that and estimate the cost of
the hash technique appropriately, but seemingly it's failing to do so.
Since you didn't provide EXPLAIN ANALYZE output, though, it's hard
to be sure.
> 3) is there a reason why the planner doesn't seem to recognize the condition
> when the hash table won't fit in the current work_mem, and choose a
> low-memory plan instead?
That's the question, all right. I wonder if it's got something to do
with the wide-varchar nature of the join key ... but again that's just
speculation with no facts. Please show us EXPLAIN ANALYZE results
for the hash plan with both small and large work_mem, as well as for
the nestloop plan.
regards, tom lane
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: tgl@sss.pgh.pa.us, peter@labkey.com
Subject: Re: Questions on query planner, join types, and work_mem
In-Reply-To: <18551.1280275502@sss.pgh.pa.us>
* 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