Am I just being naive, or couldn't this be solved by adding min/max
boolean flags to pg_aggregates and the appropriate syntax to CREATE
AGGREGATE? That would just leave the simple matter of the index scanning
code </sarcasm>.
BTW, I recently tried to do something like this...
SELECT key, blah, foo, bar, scoring_function(blah) AS score INTO TEMP t1 FROM blah;
SELECT key, blah, foo, bar
INTO TEMP info_for_max_scoring_entry_for_each_key
FROM t1
WHERE t1.score = (SELECT score FROM t1 AS inner_t1 WHERE
inner_t1.key = t1.key ORDER BY score DESC LIMIT 1)
;
The performance was horrid. I ended up building a middle table using
SELECT key, max(score) INTO TEMP t2 FROM t1 GROUP BY key;
and joining with that to build the final table I wanted. So it seems the
ORDER/LIMIT hack doesn't work well at all except in limited situations.
On Wed, Jun 11, 2003 at 03:11:26PM -0500, Bruno Wolff III wrote:
> On Wed, Jun 11, 2003 at 11:59:36 -0700,
> Dennis Gearon <gearond@cvc.net> wrote:
> > I guess the question is, are other big iron data bases using indexes on
> > MAX/MIN functions, and how are they doing it?
>
> It is easier for them to do it because they don't have to worry about
> a function named max not really being a maximum.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
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?"