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

On Thu, Jun 12, 2003 at 17:17:19 -0500,
"Jim C. Nasby" <jim(at)nasby(dot)net> wrote:
> 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>.

There are other potential aggregates of this type. You would be better
off adding an operator (class?) than a flag.

>
> 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.

Unless there was a combined index on key and score I would expect
the form you ended up using to be the fastest way to do it. With
a combined index, distinct on would probably be a bit faster (epsecially
if there were lots of values with the same key).