From: Greg Stark <gsstark@mit.edu>
To: Cristian Prieto <cristian@clickdiario.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Any way to optimize GROUP BY queries?
Date: 19 Dec 2005 15:47:35 -0500
Message-ID: <87wti0ak60.fsf@stark.xeocode.com> (raw)
In-Reply-To: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
References: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
"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
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: gsstark@mit.edu, cristian@clickdiario.com
Subject: Re: Any way to optimize GROUP BY queries?
In-Reply-To: <87wti0ak60.fsf@stark.xeocode.com>
* 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