DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=m3G3D7NSzLSpf7NUqgk6j+S/7qC3EOyCwT0iTP497U9+5c6jRRc+y1bIEaMlkqYCkV
	kyqiKvT934p1aOqyXa+1cUaB3vQohHKgQNK9SJMslGNjsZYdP1BXkGje078zBC9vPfb4
	CX/eQ0XG8wCpeF7rnlTgZFW7mzNi3hR3kMNoY=
MIME-Version: 1.0
In-Reply-To: <bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
	<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
	<bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
Date: Thu, 4 Feb 2010 16:57:21 -0500
Message-ID: <603c8f071002041357k72da49a7yc5ecb7a78ef6c52f@mail.gmail.com>
Subject: Re: Slow query: table iteration (8.3)
From: Robert Haas <robertmhaas@gmail.com>
To: Glenn Maynard <glenn@zewt.org>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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> wrot=
e:
>> 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 =3D s.id) AND ($1 IS NULL OR r.user_card_id =3D $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. =A0Mine 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