To: "Cristian Prieto" <cristian@clickdiario.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Any way to optimize GROUP BY queries?
References: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
In-Reply-To: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 19 Dec 2005 15:47:35 -0500
Message-ID: <87wti0ak60.fsf@stark.xeocode.com>
Lines: 25
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Cristian Prieto" <cristian@clickdiario.com> writes:

> SELECT adv, pub, web, country, date_trunc('hour', tiempo), sum(num)
> FROM mytmp GROUP BY adv, pub, web, country, date_trunc('hour', tiempo)
> 
> I've tried to create index in different columns but it seems that the group
> by clause doesn't use the index in any way.

If you had an index on < adv,pub,web,country,date_trunc('hour',tiemp) > then
it would be capable of using the index however it would choose not to unless
you forced it to. Using the index would be slower.

> Is around there any stuff to accelerate the group by kind of clauses?

Increase your work_mem (or sort_mem in older postgres versions), you can do
this for the server as a whole or just for this one session and set it back
after this one query. You can increase it up until it starts causing swapping
at which point it would be counter productive.

If increasing work_mem doesn't allow a hash aggregate or at least an in-memory
sort to handle it then putting the pgsql_tmp directory on a separate spindle
might help if you have any available.

-- 
greg