From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Two Index Questions
Date: Fri, 19 Jul 2002 14:35:42 -0400
Message-ID: <14339.1027103742@sss.pgh.pa.us> (raw)
In-Reply-To: <200207191112.49885.josh@agliodbs.com>
References: <200207190933.59283.josh@agliodbs.com>
<200207191028.18547.josh@agliodbs.com>
<20020719175638.GA14836@wolff.to>
<200207191112.49885.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Tom, why doesn't MAX() use an index?
Don't tell me you haven't seen that discussed many times before :-(
Because Postgres uses an extensible set of aggregate functions, we treat
all aggregates as "black boxes": the implementation strategy is always
to pass all the specified values through the aggregate.
Special-casing MIN and MAX would be nice from the point of view of
performance, but there's this little problem that our sets of datatypes
and index types are also extensible. We'd need to devise some
non-hard-wired way of identifying which aggregate functions are related
to the sort orderings of what indexes.
Finally, the transformation into an optimized form is just not that easy
to do automatically in the general case --- it's easy enough if you
write "SELECT max(col) FROM tab", but how about "SELECT max(col),
min(col) FROM tab"? What if there are WHERE clauses (with or without
constraints on col)? And the GROUP BY case that we started this
discussion with is *very* nontrivial.
This issue is on the TODO list, but given that the ORDER BY/LIMIT
workaround is available (and offers more functionality than MAX/MIN
anyway), I don't think it's a very high-priority problem. We've got
plenty of TODO items for which there is no good workaround...
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, bruno@wolff.to
Subject: Re: Two Index Questions
In-Reply-To: <14339.1027103742@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