Re: [PERFORM] 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
On Fri, Feb 5, 2010 at 8:35 PM, Glenn Maynard gl...@zewt.org wrote: On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga yebhavi...@gmail.com 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 users and score and the sql function has an order by that matches the index, so the planner can avoid a sort by accessing the test table using the index. That's why the index exists. The point is that the window function doesn't use the index in this way, and (I think) does a complete index scan. It's not just about avoiding a sort, but avoiding touching all of the irrelevant data in the index and just index searching for each user_id. The window function appears to scan the entire index. In principle, it could skip all of the rank() 1 data with an index search, which I'd expect to help many uses of rank(); I assume that's just hard to implement. Yeah. The window function stuff is all pretty new, and I seem to recall some discussion around the fact that it's not all as well-optimized as it could be yet. Maybe someone will feel the urge to take a whack at that for 9.1. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
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 100ms. Hi Glenn, About cached plans of SQL functions: from the source of function.c 00067 /* 00068 * An SQLFunctionCache record is built during the first call, 00069 * and linked to from the fn_extra field of the FmgrInfo struct. 00070 * 00071 * Note that currently this has only the lifespan of the calling query. 00072 * Someday we might want to consider caching the parse/plan results longer 00073 * than that. 00074 */ So it is planned at every call of SELECT high_score_for_user(u.id) FROM test_users u; 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 users and score and the sql function has an order by that matches the index, so the planner can avoid a sort by accessing the test table using the index. In this particular case, that means that the plan is optimal; no unneeded tuples are processed and the (function) plan complexity is logaritmic on the size of the test relation, you can't get it any better than that. In short: the lack of an actual parameter in the test case did not result in an inferior plan. So using a dynamic constructed query string in pl/pgsql to 'force' replanning during iteration cannot be faster than this sql function. It is possible to make the performance if this function worse by disabling indexscans: 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 $$ SET enable_indexscan = off; Now the query time with test_users is over a second. So maybe the converse could also be true in your production setup using the same technique. regards, Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga yebhavi...@gmail.com 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 users and score and the sql function has an order by that matches the index, so the planner can avoid a sort by accessing the test table using the index. That's why the index exists. The point is that the window function doesn't use the index in this way, and (I think) does a complete index scan. It's not just about avoiding a sort, but avoiding touching all of the irrelevant data in the index and just index searching for each user_id. The window function appears to scan the entire index. In principle, it could skip all of the rank() 1 data with an index search, which I'd expect to help many uses of rank(); I assume that's just hard to implement. I'll probably be implementing the temporary functions approach tonight, to help Postgres optimize the function. Maybe some day, Postgres will be able to inline functions in this case and that won't be needed... -- Glenn Maynard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
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
Re: [PERFORM] Slow query: table iteration (8.3)
2010/2/4 Grzegorz Jaśkiewicz gryz...@gmail.com: 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 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. -- Glenn Maynard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
On Thu, Feb 4, 2010 at 3:24 AM, Glenn Maynard gl...@zewt.org wrote: On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas robertmh...@gmail.com 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; this SELECT will only find the N highest scores, since the LIMIT applies to the whole results. Mine finds the highest scores for each stage (steps), since the scope of the LIMIT is each call of the function (eg. find the top score for each stage as opposed to find the top five scores for each stage). That's the only reason I used a function at all to begin with--I know no way to do this with a plain SELECT. Oh, I get it. Yeah, I don't think you can do that without LATERAL(), which we don't have, unless the window-function thing works... ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
On Thu, Feb 4, 2010 at 4:09 AM, Glenn Maynard gl...@zewt.org 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 scale for this case. I'll simplify to give an actual test case. create table test_users (id serial primary key); insert into test_users (id) select generate_series(1, 1000); create table test (id serial primary key, score integer, user_id integer); insert into test (user_id, score) select s.id, random() * 100 from (select generate_series(1, 1000) as id) as s, generate_series(1, 1000); create index test_1 on test (score); create index test_2 on test (user_id, score desc); analyze; This generates a thousand users, with a thousand scores each. This finds the top score for each user (ignoring the detail of duplicate scores; easy to deal with): SELECT sub.id FROM ( SELECT t.id, rank() OVER (PARTITION BY t.user_id ORDER BY score DESC) AS rank FROM test t ) AS sub WHERE rank = 1; This does use the test_2 index (as intended), but it's still very slow: 11 seconds on my system. It seems like it's doing a *complete* scan of the index, generating ranks for every row, and then filters out all but the first of each rank. That means it scales linearly with the total number of rows. All it really needs to do is jump to each user in the index and pull out the first entry (according to the score desc part of the test_2 index), which would make it scale linearly with the number of users. 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 100ms. I think I'm stuck with either creating temporary functions with the constants already replaced, or creating an SQL function that evaluates a brand new query as a string as Yeb suggested. -- Glenn Maynard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
Glenn Maynard wrote: On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga yhavi...@gmail.com 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 function 1500 times, and each time it returns no data, because there simply aren't any results for these parameters. Hmm.. first posting on a pg mailing list and I make this mistake.. What an introduction :-[ Checked the source and indeed for every plan node the number of tuples that result from it are counted. In most cases this is the number of records that match the qualifiers (where clause/join conditions) so that was in my head: actual rows = rows that match where, and without where I'd expected the actual rows to reflect the total number of rows in the table. But with a set returning functions this number is something completely different. below. The truth might be that you probably got that result by explaining the query in the function with actual parameter values. This plan differs from the one that is made when the function is called from sql and is planned (once) without parameters, and in that case the plan is probably different. Yeah. It would help a lot if EXPLAIN could show query plans of functions used by the statement and not just the top-level query. Like subplans are, yes. Sounds like a great future. Squinting at the output, it definitely looks like a less optimized plan; it's using a SEQSCAN instead of BITMAPHEAPSCAN. (I've attached the output.) Does the planner not optimize functions based on context? 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 query and actual parameter values and executes that new query everytime. It's not as pretty as a sql function, but would give an idea of how fast things would run with each loop replanned. Another idea is that maybe you could 'hint' the planner at planning time of the sql function by giving it some extra set commands (like set search_path but then set enable_seqscan = off) - I don't know if planning of the sql function occurs in the environment given by it's set commands, but its worth a try. Again, certainly not pretty. I can't see how to apply WITH to this. Non-recursive WITH seems like syntax sugar that doesn't do anything a plain SELECT can't do, and I don't think what I'm doing here can be done with a regular SELECT. With indeed is not a solution because the with query is executed once, so it cannot take a parameter. What about a window function on a join of stomp_steps and stomp_round with partition by on steps_id and user_card is and order by score and with row_number() your third parameter. From the docs I read that window functions cannot be part of the where clause: an extra subselect leven is needed then to filter the correct row numbers. Regards, Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
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 ms Hello Glenn, 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. Suppose that the number of rows is 2900, then 26 seconds means 100ms per function call. This still is a lot, compared to the 0.054 ms analyze result below. The truth might be that you probably got that result by explaining the query in the function with actual parameter values. This plan differs from the one that is made when the function is called from sql and is planned (once) without parameters, and in that case the plan is probably different. A way to check the plan of that query is to turn on debug_print_plan and watch the server log. It takes a bit getting used. The plan starts with CONTEXT: SQL function functionname during startup and is also recognized because in the opexpr (operator expression) one of the operands is a parameter. Important is the total cost of the top plan node (the limit). I know 8.3 is mentioned in the subject, but I think that a WITH query (http://www.postgresql.org/docs/8.4/interactive/queries-with.html) could be a good solution to your problem and may be worth trying out, if you have the possibility to try out 8.4. Regards, Yeb Havinga The inner function looks like this: CREATE FUNCTION highscores_for_steps_and_card(steps_id int, card_id int, count int) RETURNS SETOF INTEGER LANGUAGE SQL AS $$ SELECT r.id FROM stomp_round r WHERE ($1 IS NULL OR r.steps_id = $1) AND ($2 IS NULL OR r.user_card_id = $2) ORDER BY r.score DESC LIMIT $3 $$ Limit (cost=13.12..13.12 rows=1 width=8) (actual time=0.054..0.054 rows=0 loops=1) - Sort (cost=13.12..13.12 rows=1 width=8) (actual time=0.051..0.051 rows=0 loops=1) Sort Key: score Sort Method: quicksort Memory: 17kB - Bitmap Heap Scan on stomp_round r (cost=9.09..13.11 rows=1 width=8) (actual time=0.036..0.036 rows=0 loops=1) Recheck Cond: ((280 = steps_id) AND (user_card_id = 591)) - BitmapAnd (cost=9.09..9.09 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=1) - Bitmap Index Scan on stomp_round_steps_id (cost=0.00..4.40 rows=20 width=0) (actual time=0.030..0.030 rows=0 loops=1) Index Cond: (280 = steps_id) - Bitmap Index Scan on stomp_round_user_card_id (cost=0.00..4.44 rows=25 width=0) (never executed) Index Cond: (user_card_id = 591) Total runtime: 0.153 ms (12 rows) stomp_steps has about 1500 rows, so it finds 1500 high scores, one for each stage. I expected scalability issues from this on a regular drive, since it'll be doing a ton of index seeking when not working out of cache, so I expected to need to change to an SSD at some point (when it no longer easily fits in cache). However, I/O doesn't seem to be the bottleneck yet. If I run it several times, it consistently takes 26 seconds. The entire database is in OS cache (find | xargs cat: 250ms). I'm not sure why the full query (26s) is orders of magnitude slower than 1500*0.150ms (225ms). It's not a very complex query, and I'd hope it's not being re-planned every iteration through the loop. Any thoughts? Using SELECT to iterate over a table like this is very useful (and I don't know any practical alternative), but it's difficult to profile since it doesn't play nice with EXPLAIN ANALYZE. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query: table iteration (8.3)
On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga yhavi...@gmail.com 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 ms
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 function 1500 times, and each
time it returns no data, because there simply aren't any results for
these parameters.
below. The truth might be that you probably got that result by explaining
the query in the function with actual parameter values. This plan differs
from the one that is made when the function is called from sql and is
planned (once) without parameters, and in that case the plan is probably
different.
Yeah. It would help a lot if EXPLAIN could show query plans of
functions used by the statement and not just the top-level query.
A way to check the plan of that query is to turn on
debug_print_plan and watch the server log. It takes a bit getting used. The
plan starts with CONTEXT: SQL function functionname during startup and is
also recognized because in the opexpr (operator expression) one of the
operands is a parameter. Important is the total cost of the top plan node
(the limit).
Thanks.
SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s:
Squinting at the output, it definitely looks like a less optimized
plan; it's using a SEQSCAN instead of BITMAPHEAPSCAN. (I've attached
the output.)
Does the planner not optimize functions based on context? That seems
like a huge class of optimizations. The first NULLTEST can be
optimized away, since that parameter comes from a NOT NULL source (a
PK). The second NULLTEST can also be optimized away, since it's a
constant value (591). The search could be a BITMAPHEAPSCAN,
substituting the s.id value for each call, instead of a SEQSCAN. (Not
that I'm concerned about a few cheap NULLTESTs, I'm just surprised at
it using such a generic plan.)
If I create a new function with the constant parameters hard-coded,
it's back to BITMAPHEAPSCAN: 175ms. This suggests a horrible
workaround: creating temporary functions every time I make this type
of query, with the fixed values substituted textually. I'd really
love to know a less awful fix.
I know 8.3 is mentioned in the subject, but I think that a WITH query
(http://www.postgresql.org/docs/8.4/interactive/queries-with.html) could be
a good solution to your problem and may be worth trying out, if you have the
possibility to try out 8.4.
I can't see how to apply WITH to this. Non-recursive WITH seems like
syntax sugar that doesn't do anything a plain SELECT can't do, and I
don't think what I'm doing here can be done with a regular SELECT.
--
Glenn Maynard
SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
QUERY PLAN
Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4) (actual
time=1726.998..26729.717 rows=17 loops=1)
Total runtime: 26729.822 ms
DEBUG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:canSetTag true
:planTree
{SEQSCAN
:startup_cost 0.00
:total_cost 793.52
:plan_rows 2902
:plan_width 4
:targetlist (
{TARGETENTRY
:expr
{FUNCEXPR
:funcid 240532
:funcresulttype 23
:funcretset true
:funcformat 0
:args (
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
{CONST
:consttype 23
:consttypmod -1
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 79 2 0 0 ]
}
{CONST
:consttype 23
:consttypmod -1
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 1 0 0 0 ]
}
)
}
:resno 1
:resname highscores_for_steps_and_card
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual
:lefttree
:righttree
:initPlan
:extParam (b)
:allParam (b)
:scanrelid 1
}
:rtable (
{RTE
:alias
{ALIAS
:aliasname s
:colnames
}
:eref
{ALIAS
:aliasname s
:colnames (id ...)
[PERFORM] Slow query: table iteration (8.3)
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 looks like this: CREATE FUNCTION highscores_for_steps_and_card(steps_id int, card_id int, count int) RETURNS SETOF INTEGER LANGUAGE SQL AS $$ SELECT r.id FROM stomp_round r WHERE ($1 IS NULL OR r.steps_id = $1) AND ($2 IS NULL OR r.user_card_id = $2) ORDER BY r.score DESC LIMIT $3 $$ Limit (cost=13.12..13.12 rows=1 width=8) (actual time=0.054..0.054 rows=0 loops=1) - Sort (cost=13.12..13.12 rows=1 width=8) (actual time=0.051..0.051 rows=0 loops=1) Sort Key: score Sort Method: quicksort Memory: 17kB - Bitmap Heap Scan on stomp_round r (cost=9.09..13.11 rows=1 width=8) (actual time=0.036..0.036 rows=0 loops=1) Recheck Cond: ((280 = steps_id) AND (user_card_id = 591)) - BitmapAnd (cost=9.09..9.09 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=1) - Bitmap Index Scan on stomp_round_steps_id (cost=0.00..4.40 rows=20 width=0) (actual time=0.030..0.030 rows=0 loops=1) Index Cond: (280 = steps_id) - Bitmap Index Scan on stomp_round_user_card_id (cost=0.00..4.44 rows=25 width=0) (never executed) Index Cond: (user_card_id = 591) Total runtime: 0.153 ms (12 rows) stomp_steps has about 1500 rows, so it finds 1500 high scores, one for each stage. I expected scalability issues from this on a regular drive, since it'll be doing a ton of index seeking when not working out of cache, so I expected to need to change to an SSD at some point (when it no longer easily fits in cache). However, I/O doesn't seem to be the bottleneck yet. If I run it several times, it consistently takes 26 seconds. The entire database is in OS cache (find | xargs cat: 250ms). I'm not sure why the full query (26s) is orders of magnitude slower than 1500*0.150ms (225ms). It's not a very complex query, and I'd hope it's not being re-planned every iteration through the loop. Any thoughts? Using SELECT to iterate over a table like this is very useful (and I don't know any practical alternative), but it's difficult to profile since it doesn't play nice with EXPLAIN ANALYZE. -- Glenn Maynard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
