Thread: 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
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. > >
On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@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
Attachment
Glenn Maynard wrote: > On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@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
On Fri, Jan 29, 2010 at 10:49 PM, Glenn Maynard <glenn@zewt.org> 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 > > 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. I believe that the time for the seq-scan node doesn't include the time to generate the outputs, which is where all the function calls are. As a general rule, I have found that function calls are reaaaaally slow, and that calling a function in a loop is almost always a bad idea. You didn't mention what PG version you're running, but I believe that with a sufficiently new version (8.4?) it'll actually inline SQL functions into the invoking query, which will probably be lots faster. If not, you can inline it manually. 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 You might even break it into two cases: SELECT r.id FROM stomp_steps s, stomp_round r WHERE r.steps_id = s.id AND ($1 IS NULL OR r.user_card_id = $1) UNION ALL SELECT r.id FROM stomp_steps s, stomp_round r WHERE s.id IS NULL AND ($1 IS NULL OR r.user_card_id = $1) ORDER BY r.score DESC LIMIT $2 Or if s.id can't really be NULL: SELECT r.id FROM stomp_steps s, stomp_round r WHERE r.steps_id = s.id AND ($1 IS NULL OR r.user_card_id = $1) ORDER BY r.score DESC LIMIT $2 These kinds of rewrites allow the query planner progressively more flexibility - to use a hash or merge join, for example, instead of a nested loop. And they eliminate overhead. You'll have to play around with it and see what works best in your particular environment, but in general, I find it pays big dividends to avoid wrapping these kinds of logic bits inside a function. ...Robert
On Tue, Feb 2, 2010 at 5:06 AM, Yeb Havinga <yebhavinga@gmail.com> 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 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, or just have the code generate a function on the fly, and then
delete it. For example:
CREATE FUNCTION tmp_highscores_for_steps_and_card_PID(steps_id int)
RETURNS SETOF INTEGER LANGUAGE SQL AS $$
SELECT r.id FROM stomp_round r
WHERE ($1 IS NULL OR r.steps_id = $1) AND r.user_card_id = 591
ORDER BY r.score DESC LIMIT 1
$$;
SELECT tmp_highscores_for_steps_and_card_PID(s.id) FROM stomp_steps s;
DROP FUNCTION tmp_highscores_for_steps_and_card_PID(int);
An ugly hack, but it'd unblock things, at least. (Or, I hope so. I
do have other variants of this, for things like "high scores in your
country", "your 5 most recent high scores", etc. That's why I'm doing
this dynamically like this, and not just caching high scores in
another table.)
> 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.
Someone suggested window functions for this back when I was designing
it, and I looked at them. I recall it being very slow, always doing a
seq scan, and it seemed like this wasn't quite what windowing was
designed for...
--
Glenn Maynard
On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas <robertmhaas@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. eg. CREATE FUNCTION test(int) RETURNS SETOF INTEGER LANGUAGE SQL AS $$ SELECT generate_series(100 * $1, 100 * $1 + 5) LIMIT 2; $$; CREATE TABLE test_table(id integer primary key); INSERT INTO test_table SELECT generate_series(1, 5); SELECT test(t.id) FROM test_table t; If there's a way to do this without a helper function (that can optimize to index scans--I'm not sure 8.4's windowing did, need to recheck), I'd really like to know it. > And they eliminate overhead. I assumed that function calls within a SELECT would be inlined for optimization before reaching the planner--that's why I was surprised when it was falling back on a seq scan, and not optimizing for the context. I'm using 8.3. I see "Inline simple set-returning SQL functions in FROM clauses" in the 8.4 changelog; I'm not sure if that applies to this, since this set-returning SQL function isn't in the FROM clause. -- Glenn Maynard
isn't that possible with window functions and cte ? rank, and limit ?
2010/2/4 Grzegorz Jaśkiewicz <gryzman@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
On Thu, Feb 4, 2010 at 3:24 AM, Glenn Maynard <glenn@zewt.org> wrote: > On Wed, Feb 3, 2010 at 10:05 PM, Robert Haas <robertmhaas@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
On Thu, Feb 4, 2010 at 4:09 AM, Glenn Maynard <glenn@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() * 1000000 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
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
On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga <yebhavinga@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
On Fri, Feb 5, 2010 at 8:35 PM, Glenn Maynard <glenn@zewt.org> wrote: > On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga <yebhavinga@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
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