Re: Nested query performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas
<heikki.linnakangas@xxxxxxxxxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux