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