On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas <robertmhaas@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
