Skip site navigation (1) Skip section navigation (2)

Any way to optimize GROUP BY queries?

From: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Any way to optimize GROUP BY queries?
Date: 2005-12-19 17:30:25
Message-ID: 00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local (view raw or flat)
Thread:
Lists: 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.

 

 

 

Responses

pgsql-performance by date

Next:From: James KloDate: 2005-12-19 19:10:50
Subject: Re: make bulk deletes faster?
Previous:From: Luke LonerganDate: 2005-12-19 17:30:21
Subject: Re: PostgreSQL and Ultrasparc T1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group