From: Glenn Maynard <glenn@zewt.org>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Thu, 4 Feb 2010 03:24:31 -0500
Message-ID: <bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com> (raw)
In-Reply-To: <603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
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.
eg.
CREATE FUNCTION test(int) RETURNS SETOF INTEGER LANGUAGE SQL AS $$
SELECT generate_series(100 * $1, 100 * $1 + 5) LIMIT 2;
$$;
CREATE TABLE test_table(id integer primary key);
INSERT INTO test_table SELECT generate_series(1, 5);
SELECT test(t.id) FROM test_table t;
If there's a way to do this without a helper function (that can
optimize to index scans--I'm not sure 8.4's windowing did, need to
recheck), I'd really like to know it.
> And they eliminate overhead.
I assumed that function calls within a SELECT would be inlined for
optimization before reaching the planner--that's why I was surprised
when it was falling back on a seq scan, and not optimizing for the
context.
I'm using 8.3. I see "Inline simple set-returning SQL functions in
FROM clauses" in the 8.4 changelog; I'm not sure if that applies to
this, since this set-returning SQL function isn't in the FROM clause.
--
Glenn Maynard
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: glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <bd36f99e1002040024i24a2c78vfad45611946c2ae9@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