Re: Slow query: table iteration (8.3)

Glenn Maynard wrote:
> CREATE FUNCTION high_score_for_user(user_id int) RETURNS SETOF INTEGER
> LANGUAGE SQL AS $$
>        SELECT t.id FROM test t
>        WHERE t.user_id = $1
>        ORDER BY t.score DESC LIMIT 1
> $$;
> SELECT high_score_for_user(u.id) FROM test_users u;
>
> runs in 100ms.
>
Though it doesn't solve your problem without changing result format, but
what about

aap=# explain select u.id, ARRAY(select t.id from test t where
t.user_id=u.id order by t.score desc limit 2) as high from test_users u;
                                       QUERY
PLAN
-----------------------------------------------------------------------------------------
 Seq Scan on test_users u  (cost=0.00..3290.84 rows=1000 width=4)
   SubPlan 1
     ->  Limit  (cost=0.00..3.28 rows=2 width=8)
           ->  Index Scan using test_2 on test t  (cost=0.00..1637.92
rows=1000 width=8)
                 Index Cond: (user_id = $0)
(5 rows)

  id  |      high
------+-----------------
    1 | {641,896}
    2 | {1757,1167}
    3 | {2765,2168}
    4 | {3209,3674}
    5 | {4479,4993}

regards,
Yeb Havinga