Re: Nested query performance issue

On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> SELECT s.* FROM score s, game g
>> WHERE s.game_id = g.id AND
>>  s.id IN (
>>    SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score
>> DESC LIMIT 1
>>  );
>
> You don't really need the join with game here, simplifying this into:
>
> SELECT s.* FROM score s
> WHERE s.id IN (
>    SELECT s2.id FROM score s2 WHERE s2.game_id=s.game_id ORDER BY s2.score
> DESC LIMIT 1
> );
>
> I don't think it makes it any faster, though.

It's about 10% faster for me. I'm surprised the planner can't figure
out that this join is redundant.

> SELECT * FROM (
>  SELECT s.*, rank() OVER (PARTITION BY s.game_id ORDER BY score DESC) AS
> rank FROM score s
> ) AS sub WHERE rank <= 5;
>
> but I'm not sure how much faster it is. At least here on my laptop it does a
> full index scan on score, which may or may not be faster than just picking
> the top N values for each game using the index.

I'll definitely check this out when 8.4 is released.

> You can do that approach with a SQL function:
>
> CREATE FUNCTION topnscores(game_id int , n int) RETURNS SETOF score LANGUAGE
> SQL AS $$
> SELECT * FROM score s WHERE s.game_id = $1 ORDER BY score DESC LIMIT $2
> $$;
>
> SELECT (sub.ts).id, (sub.ts).score, (sub.ts).game_id
> FROM (SELECT topnscores(g.id, 5) ts FROM game g) sub;
("as ts", for anyone trying this at home)

Thanks--this one runs in 32ms, which seems about right compared
against the original fast LIMIT 1 version.

I see a slight improvement if I mark the function stable: 31.9ms to
31.2; minor but consistent. Just out of curiosity, any explanations
for this difference? I don't see any change in the resulting query
plan, but the plan doesn't enter the function call.

--
Glenn Maynard