Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Adam Erickson" <adamre@cox.net>, <pgsql-novice@postgresql.org>
Subject: Re: Aggregates and Indexes
Date: Fri, 2 Aug 2002 13:20:56 -0700
References: <ALEKKHACBIAEBNBPFKPCCEFHEDAA.adamre@cox.net>
In-Reply-To: <ALEKKHACBIAEBNBPFKPCCEFHEDAA.adamre@cox.net>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208021320.56468.josh@agliodbs.com>


Adam,

> I do a lot of reporting based on such SQL statements.  Counting rows from
> large datasets.  Since the PG gurus don't seem to think this is such a big
> deal can someone enlighten me as to why?

I am not a core developer, but I will take a crack at your question anyway=
=20
based on my personal knowledge.  I am sure that Tom, Bruce, or Stephan will=
=20
correct my mistaken assumptions.

Actually, the inability to index MAX, MIN, and COUNT is an annoyance I thin=
k=20
everyone would like to fix; it's just that the technical challenge is much=
=20
greater than the benefit for the core team.   If you know a programmer who=
=20
wants to tackle it, go ahead.

Presumably you've already read the many previous e-mails on why it is a=20
techincal challenge.

Now, even if that challenge were solved, indexing for aggregates would stil=
l=20
be of limited usefulness because:

Few (if any) RDBMSs can index for SUM, STDEV, or other "calcuation"=20
aggregates.  This is because the value of every single record must be incud=
ed=20
and estimates are not possible, so the performance gain from using an index=
=20
is infinitessimal except for those RDBMSs with very slow file access times.

For Postgres custom aggregates, using a standard index is impossible, for=
=20
reasons I think are obvious.

That leaves MAX, MIN, and COUNT.    All of these aggregates should, in an=
=20
ideal world, be index-responsive for large data sets.  Once again, for smal=
l=20
data sets or subsets, indexes are not useful.   And there is a workaround f=
or=20
Min and Max.

So what we'd be looking at is either developing a special parser routine fo=
r=20
MIN, MAX, and COUNT (and them only) just to index for those aggregates, or=
=20
coming up with a new type of index just for aggregates.   The first approac=
h=20
is a bit of kludge that would require a lot of debugging; the second is=20
probably the best long-term solution, but would require a great deal of=20
innovation.


--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco