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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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