index for group by

From: Patrick Scharrenberg <pittipatti(at)web(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: index for group by
Date: 2008-07-22 11:18:30
Message-ID: 4885C206.3040800@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

is there a way to speedup "group by" queries with an index?

In particular if I have a table like this:

CREATE TABLE data
(
id1 integer,
id2 integer,
somedata character varying,
ts timestamp with time zone
);

where continously data is logged about "id1" and "id2" into "somedata",
together with the timestamp when it was logged.

So I have multiple rows with the same id1 and id2 but different
timestamp (and data maybe).

At the moment I have ~40.000.000 rows in that table so doing a

SELECT id1, id2 FROM data GROUP BY id1, id2;

takes some time (~10 minutes)
and return about 1.000.000 rows.

I created an index on both colums id1 and id2 (together) which takes
about 800 MB but doesn't speedup things.
In fact it even doesn't seem to be used.

Is there any way to speedup this "group by" or does it seem more likely
that I have a conceptional flaw?

regards
Patrick

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2008-07-22 11:27:24 Re: index for group by
Previous Message A. Kretschmer 2008-07-22 09:06:07 Re: How to Select a Tupl by Nearest Date