Re: memory usage of group by select

Anthony wrote:
> On Tue, Dec 29, 2009 at 3:41 PM, Anthony <osm(at)inbox(dot)org> wrote:
>
> > I'm running a group by query on a table with over a billion rows and my
> > memory usage is seemingly growing without bounds. Eventually the mem usage
> > exceeds my physical memory and everything starts swapping.
> >
>
> I guess I didn't ask my question. Is this expected behavior? Is there any
> way for me to adjust my settings to avoid using so much memory?

It's expecting 85k distinct groups. If that's not accurate, then
HashAggregate would use more memory than expected. See if you can make
it work by setting enable_hashagg = off.

If that works, good -- the real solution is different. Maybe you need
to ANALYZE more, or increase the size of the stats bin for this column.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.