From: Greg Stark <gsstark@mit.edu>
To: josh@agliodbs.com
Cc: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Slow sub-selects, max and count(*)
Date: 28 Mar 2004 01:31:31 -0500
Message-ID: <87brmhtrcs.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200402042113.01369.josh@agliodbs.com>
References: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000>
<200402042113.01369.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Max() and Count() cannot use indexes for technical reasons. Browse through
> the archives of SQL, PERFORM, and HACKERS for about 12,000 discussions on the
> subject.
Please don't confuse the issue by throwing Max() and Count() into the same
basket.
The issue with Min()/Max() is that the result could be generated efficiently
by scanning indexes but it's just hard, especially given generic aggregate
functions, and the work simply hasn't been done, or even started, yet.
The issue with Count() is that people want the result to be cached in a single
per-table counter, but that can't be done as simply as that because of
transactions. People have discussed complex solutions to this but it's a much
more complex problem than it appears.
They're really two entirely separate issues.
--
greg
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: gsstark@mit.edu, josh@agliodbs.com, richard@ibisaustralia.com
Subject: Re: Slow sub-selects, max and count(*)
In-Reply-To: <87brmhtrcs.fsf@stark.xeocode.com>
* 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