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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
