From: Robert Haas <robertmhaas@gmail.com>
To: Glenn Maynard <glenn@zewt.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Thu, 4 Feb 2010 16:57:21 -0500
Message-ID: <603c8f071002041357k72da49a7yc5ecb7a78ef6c52f@mail.gmail.com> (raw)
In-Reply-To: <bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
<bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <603c8f071002041357k72da49a7yc5ecb7a78ef6c52f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox