pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAny way to optimize GROUP BY queries?
4+ messages / 4 participants
[nested] [flat]
* Any way to optimize GROUP BY queries?
@ 2005-12-19 17:30 Cristian Prieto <cristian@clickdiario.com>
0 siblings, 1 reply; 4+ messages in thread
From: Cristian Prieto @ 2005-12-19 17:30 UTC (permalink / raw)
To: pgsql-performance
I have the following table:
CREATE TABLE mytmp (
Adv integer,
Pub integer,
Web integer,
Tiempo timestamp,
Num integer,
Country varchar(2)
);
CREATE INDEX idx_mytmp ON mytmp(adv, pub, web);
And with 16M rows this query:
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.
Is around there any stuff to accelerate the group by kind of clauses?
Thanks a lot.
Attachments:
image001.gif (image/gif, 7.1K) download
view image
* Re: Any way to optimize GROUP BY queries?
@ 2005-12-19 20:47 Greg Stark <gsstark@mit.edu>
parent: Cristian Prieto <cristian@clickdiario.com>
0 siblings, 1 reply; 4+ messages in thread
From: Greg Stark @ 2005-12-19 20:47 UTC (permalink / raw)
To: Cristian Prieto <cristian@clickdiario.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Any way to optimize GROUP BY queries?
@ 2005-12-20 19:47 Jim C. Nasby <jnasby@pervasive.com>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2005-12-20 19:47 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Cristian Prieto <cristian@clickdiario.com>; pgsql-performance
On Mon, Dec 19, 2005 at 03:47:35PM -0500, Greg Stark wrote:
> 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.
Just remember that work_memory is per-operation, so it's easy to push
the box into swapping if the workload increases. You didn't say how much
memory you have, but I'd be careful if work_memory * max_connections
gets very much larger than your total memory.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Any way to optimize GROUP BY queries?
@ 2005-12-20 21:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-12-20 21:44 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Greg Stark <gsstark@mit.edu>; Cristian Prieto <cristian@clickdiario.com>; pgsql-performance
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Mon, Dec 19, 2005 at 03:47:35PM -0500, Greg Stark wrote:
>> 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.
> Just remember that work_memory is per-operation, so it's easy to push
> the box into swapping if the workload increases. You didn't say how much
> memory you have, but I'd be careful if work_memory * max_connections
> gets very much larger than your total memory.
It's considered good practice to have a relatively small default
work_mem setting (in postgresql.conf), and then let individual sessions
push up the value locally with "SET work_mem" if they are going to
execute queries that need it. This works well as long as you only have
one or a few such "heavy" sessions at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-20 21:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-19 17:30 Any way to optimize GROUP BY queries? Cristian Prieto <cristian@clickdiario.com>
2005-12-19 20:47 ` Greg Stark <gsstark@mit.edu>
2005-12-20 19:47 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-20 21:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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