Re: Generalizing max and min

On Wed, Jun 11, 2003 at 07:31:22PM -0500, Bruno Wolff III wrote:
> The hard problem is still trying to decide how to take advantage of the
> index when it is available. Sometimes an index scan should be used,
> sometimes a sequential scan and sometimes a query with multiple
> aggregates should get broken up into multiple subqueries so that
> multiple indexes can be used.

Keep in mind that with a BTREE you don't need to scan the index, you
only need to run down the left or right side of the tree (granted, an
over-simplification in the case of MVCC, but you get the point).

Even if you don't have the ideal index, you can still avoid a full index
scan. For example:
CREATE INDEX .. (a, b);
SELECT max(b) ...;

Instead of scanning the entire index, you only need to get the last
(assuming asc index) tuple for each value of a, and take the maximum of
those (of course you don't have to store the set of max for each a; you
can do a simple comparison each time you get a new last tuple).
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"