Re: index for group by

am  Tue, dem 22.07.2008, um 13:18:30 +0200 mailte Patrick Scharrenberg folgendes:
> 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;


without a where-clause every select forces a seq-scan.


> 
> 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.

The database has to read all rows, an index can't help in this case.


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

Hard to say...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net