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
On Fri, Feb 5, 2010 at 8:35 PM, Glenn Maynard wrote:
> On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga wrote:
>> and the cache is used between each row of test_users. The plan is with a
>> parameter, that means the optimizer could not make use of an actual value
>> during planning. However, your test
On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga wrote:
> and the cache is used between each row of test_users. The plan is with a
> parameter, that means the optimizer could not make use of an actual value
> during planning. However, your test case is clever in the sense that there
> is an index on us
Glenn Maynard wrote:
The function version:
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 10
On Thu, Feb 4, 2010 at 4:09 AM, Glenn Maynard wrote:
> But I'll be dropping this db into 8.4 soon to see if it helps
> anything, and I'll check again (and if it's still slow I'll post more
> details). It's been a while and I might just have been doing
> something wrong.
Windowing doesn't want to
On Thu, Feb 4, 2010 at 3:24 AM, Glenn Maynard wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas wrote:
>> Rewriting it as a join will likely be faster still:
>>
>> SELECT r.id FROM stomp_steps s, stomp_round r WHERE (s.id IS NULL OR
>> r.steps_id = s.id) AND ($1 IS NULL OR r.user_card_id = $1
2010/2/4 Grzegorz Jaśkiewicz :
> isn't that possible with window functions and cte ?
> rank, and limit ?
It is, but again I tried that when I originally designed this and I
think it ended up using seq scans, or at least being slow for some
reason or other.
But I'll be dropping this db into 8.4 so
isn't that possible with window functions and cte ?
rank, and limit ?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas wrote:
> Rewriting it as a join will likely be faster still:
>
> SELECT r.id FROM stomp_steps s, stomp_round r WHERE (s.id IS NULL OR
> r.steps_id = s.id) AND ($1 IS NULL OR r.user_card_id = $1) ORDER BY
> r.score DESC LIMIT $2
That's not the same; thi
On Tue, Feb 2, 2010 at 5:06 AM, Yeb Havinga wrote:
> I believe it does for (re) binding of parameter values to prepared
> statements, but not in the case of an sql function. To test an idea, there
> might be a workaround where you could write a pl/pgsql function that makes a
> string with the quer
On Fri, Jan 29, 2010 at 10:49 PM, Glenn Maynard wrote:
> Hitting a performance issues that I'm not sure how to diagnose.
>
> SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
> Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4)
> (actual time=26509.919..26509.919
Glenn Maynard wrote:
On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga wrote:
Stomp_steps is analyzed to 2902 rows but when you run the query the actual
rows are 0. This means that the highscore function is not called or the
number 0 is incorrect.
This SELECT returns 0 rows: it calls the fun
On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga wrote:
> Glenn Maynard wrote:
>> SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
>> Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4)
>> (actual time=26509.919..26509.919 rows=0 loops=1)
>> Total runtime: 26509.972 m
Glenn Maynard wrote:
Hitting a performance issues that I'm not sure how to diagnose.
SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4)
(actual time=26509.919..26509.919 rows=0 loops=1)
Total runtime: 26509.972
Hitting a performance issues that I'm not sure how to diagnose.
SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4)
(actual time=26509.919..26509.919 rows=0 loops=1)
Total runtime: 26509.972 ms
The inner function
15 matches
Mail list logo
