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