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

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Index not being used in MAX function (7.2.3)
Date: 2003-06-12 22:17:19
Message-ID: 20030612221719.GS40542@flake.decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)cvc(dot)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(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?"

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim C. Nasby 2003-06-12 22:26:11 Re: Index not being used in MAX function (7.2.3)
Previous Message Roland Glenn McIntosh 2003-06-12 22:13:29 How can I insert a UTF-8 character with psql?