Re: Questions on query planner, join types, and work_mem

Peter Hussey <peter(at)labkey(dot)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