Re: Slow query: table iteration (8.3)

On Thu, Feb 4, 2010 at 3:24 AM, Glenn Maynard <glenn@zewt.org> wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Rewriting it as a join will likely be faster still:
>>
>> SELECT r.id FROM stomp_steps s, stomp_round r WHERE (s.id IS NULL OR
>> r.steps_id = s.id) AND ($1 IS NULL OR r.user_card_id = $1) ORDER BY
>> r.score DESC LIMIT $2
>
> That's not the same; this SELECT will only find the N highest scores,
> since the LIMIT applies to the whole results.  Mine finds the highest
> scores for each stage (steps), since the scope of the LIMIT is each
> call of the function (eg. "find the top score for each stage" as
> opposed to "find the top five scores for each stage").
>
> That's the only reason I used a function at all to begin with--I know
> no way to do this with a plain SELECT.

Oh, I get it.  Yeah, I don't think you can do that without LATERAL(),
which we don't have, unless the window-function thing works...

...Robert