From: Yeb Havinga <yebhavinga@gmail.com>
To: Glenn Maynard <glenn@zewt.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Tue, 23 Feb 2010 13:18:19 +0100
Message-ID: <4B83C78B.6000206@gmail.com> (raw)
In-Reply-To: <bd36f99e1002041904w37cac39jeb9d60156a06c151@mail.gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
<bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
<2f4958ff1002040028j29e907c6l47e1f5efe83b667d@mail.gmail.com>
<bd36f99e1002040109w67aa1d71hc91261d183fad111@mail.gmail.com>
<bd36f99e1002041904w37cac39jeb9d60156a06c151@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: yebhavinga@gmail.com, glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <4B83C78B.6000206@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox