Re: Index not being used in MAX function (7.2.3)

On Thu, Jun 12, 2003 at 12:08:15PM +0800, Ang Chin Han wrote:
> Just a quick idea, in CREATE AGGREGATE, add optional parameters of:
>
> 1. ORDER BY ASC|DESC|USING operator
> 2. LIMIT {count}

IMHO, I don't think it's right to focus on the ORDER BY/LIMIT hack. The
real issue here is that the best way to find a min is to grab the first
tuple in the index (granted, a bit tricker in pgsql due to MVCC), and
the best way to find a max is to grab the last tuple in the index. And
this extends beyond the simplest of min/max examples. For instance, this
technique should be used to solve grouped aggregates (assuming a
suitable index, of course), the only difference is that you don't use
the first/last tuple, you use the first/last one that matches your other
criteria.

I haven't read the source, but it seems to me what's lacking is the
ability to scan indexes in order to do this.

This becomes really important whenever pgsql gains the ability to use
multiple indexes per table (someone smack me if it can do this now and I
don't realize it), because then you could do something like

SELECT min(a), max(b), min(c), min(d)

and the query would be blazing fast if you had the right indexes on all
4 fields.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.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?"