Re: work_mem greater than 2GB issue

wickro <robwickert(at)gmail(dot)com> writes:

> Hi everyone,
>
> I have a largish table (> 8GB). I'm doing a very simple single group
> by on. I am the only user of this database. If I set work mem to
> anything under 2GB (e.g. 1900MB) the postmaster process stops at that
> value while it's peforming it's group by. There is only one hash
> operation so that is what I would expect. But anything larger and it
> eats up all memory until it can't get anymore (around 7.5GB on a 8GB
> machine). Has anyone experienced anything of this sort before.

What does EXPLAIN say for both cases? I suspect what's happening is that the
planner is estimating it will need 2G to has all the values and in fact it
would need >8G. So for values under 2G it uses a sort and not a hash at all,
for values over 2G it's trying to use a hash and failing.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!