From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Adam Erickson <adamre@cox.net>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregates and Indexes
Date: Fri, 02 Aug 2002 16:43:51 -0400
Message-ID: <15593.1028321031@sss.pgh.pa.us> (raw)
In-Reply-To: <200208021320.56468.josh@agliodbs.com>
References: <ALEKKHACBIAEBNBPFKPCCEFHEDAA.adamre@cox.net>
<200208021320.56468.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> For Postgres custom aggregates, using a standard index is impossible, for
> reasons I think are obvious.
> That leaves MAX, MIN, and COUNT. All of these aggregates should, in an
> ideal world, be index-responsive for large data sets.
While it's fairly clear how one might use an index for MAX/MIN
(basically, make the optimizer transform it into a SELECT ... ORDER BY
... LIMIT 1 operation, which can then be done with an indexscan),
I really don't see how an index can help for COUNT.
The real problem with COUNT is that any attempt to maintain such a value
on-the-fly creates a single-point bottleneck for all insertions and
deletions on the table. The perspective of most of the developers is
that that cost outweighs any possible savings from having an
instantaneous COUNT operation.
When you add in the issue that under MVCC there isn't a unique COUNT
that's the same for all transactions, it's just not worth thinking
about. (And do I need to point out that with WHERE conditions,
GROUP BY, or a variable COUNT argument, all hope of such optimization
disappears anyway? A global rowcount doesn't help in those cases.)
The MAX/MIN issue will probably be addressed someday, but since there
is a good workaround it's not very high on anyone's TODO queue. We have
many more-pressing problems.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, adamre@cox.net, pgsql-novice@postgresql.org
Subject: Re: Aggregates and Indexes
In-Reply-To: <15593.1028321031@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox