Re: Slow query: table iteration (8.3)

On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas <robertmhaas(at)gmail(dot)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