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)
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 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)
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
Re: [PERFORM] Any better plan for this query?..
I'm sorry, but I'm confused. Everyone keeps talking about connection pooling, but Dimitri has said repeatedly that each client makes a single connection and then keeps it open until the end of the test, not that it makes a single connection per SQL query. Connection startup costs shouldn't be an issue. Am I missing something here? test(N) starts N clients, each client creates a single connection and hammers the server for a while on that connection. test(N) is run for N=1,2,4,8...256. This seems like a very reasonable test scenario. -- 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] performance for high-volume log insertion
On Fri, May 1, 2009 at 8:29 PM, PFC li...@peufeu.com wrote: Roundtrips can be quite fast but they have a hidden problem, which is that everything gets serialized. The client and server will serialize, but what usually matters most is avoiding serializing against disk I/O--and that's why write-back caching exists. There's still a benefit to pipelining (not everything the db might need to read to complete the write will always be in cache), but if everything was being serialized it'd be an order of magnitude worse. That's why running each insert in a separate transaction is so much slower; in that case, it *will* serialize against the disk (by default). -- 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] performance for high-volume log insertion
On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost sfr...@snowman.net wrote: Yes, as I beleive was mentioned already, planning time for inserts is really small. Parsing time for inserts when there's little parsing that has to happen also isn't all *that* expensive and the same goes for conversions from textual representations of data to binary. We're starting to re-hash things, in my view. The low-hanging fruit is doing multiple things in a single transaction, either by using COPY, multi-value INSERTs, or just multiple INSERTs in a single transaction. That's absolutely step one. This is all well-known, covered information, but perhaps some numbers will help drive this home. 4 inserts into a single-column, unindexed table; with predictable results: separate inserts, no transaction: 21.21s separate inserts, same transaction: 1.89s 40 inserts, 100 rows/insert: 0.18s one 4-value insert: 0.16s 40 prepared inserts, 100 rows/insert: 0.15s COPY (text): 0.10s COPY (binary): 0.10s Of course, real workloads will change the weights, but this is more or less the magnitude of difference I always see--batch your inserts into single statements, and if that's not enough, skip to COPY. -- 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] performance for high-volume log insertion
On Wed, Apr 22, 2009 at 4:07 PM, da...@lang.hm wrote: are these done as seperate round trips? i.e. begin send insert send insert send .. end send or as one round trip? All tests were done by constructing a file and using time psql -f 40 inserts, 100 rows/insert: 0.18s one 4-value insert: 0.16s 40 prepared inserts, 100 rows/insert: 0.15s are one of these missing a 0? Sorry, 400 * 100. All cases inserted 4 rows, and I deleted all rows between tests (but did not recreate the table). -- 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] performance for high-volume log insertion
On Wed, Apr 22, 2009 at 4:37 PM, Stephen Frost sfr...@snowman.net wrote: Thanks for doing the work. I had been intending to but hadn't gotten to it yet. I'd done similar tests recently, for some batch import code, so it was just a matter of recreating it. separate inserts, no transaction: 21.21s separate inserts, same transaction: 1.89s 40 inserts, 100 rows/insert: 0.18s one 4-value insert: 0.16s 40 prepared inserts, 100 rows/insert: 0.15s COPY (text): 0.10s COPY (binary): 0.10s What about 4 individual prepared inserts? Just curious about it. 4 inserts, one prepared statement each (constructing the prepared statement only once), in a single transaction: 1.68s I'm surprised that there's any win here at all. Also, kind of suprised about COPY text vs. binary. What was the data type in the table..? If text, that makes sense, if it was an integer or something else, I'm kind of suprised. Each row had one integer column. I expect strings to be harder to parse, since it's allocating buffers and parsing escapes, which is usually more expensive than parsing an integer out of a string. I'd expect the difference to be negligible either way, though, and I'd be interested in hearing about use cases where binary copying is enough of a win to be worth the development cost of maintaining the feature. On Wed, Apr 22, 2009 at 4:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, just to be clear: were the 40 insert cases 40 separate transactions or one transaction? I think the latter was meant but it's not 100% clear. One transaction--the only case where I ran more than one transaction was the first. -- 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] performance for high-volume log insertion
On Wed, Apr 22, 2009 at 4:53 PM, James Mansion
ja...@mansionfamily.plus.com wrote:
And I'm disagreeing with that. Single row is a given, but I think you'll
find it pays to have one
round trip if at all possible and invoking multiple prepared statements can
work against this.
You're talking about round-trips to a *local* server, on the same
system, not a dedicated server with network round-trips, right?
Blocking round trips to another process on the same server should be
fairly cheap--that is, writing to a socket (or pipe, or localhost TCP
connection) where the other side is listening for it; and then
blocking in return for the response. The act of writing to an FD that
another process is waiting for will make the kernel mark the process
as ready to wake up immediately, and the act of blocking for the
response will kick the scheduler to some waiting process, so as long
as there isn't something else to compete for CPU for, each write/read
will wake up the other process instantly. There's a task switching
cost, but that's too small to be relevant here.
Doing 100 local round trips, over a pipe: 5.25s (5 *microseconds*
each), code attached. The cost *should* be essentially identical for
any local transport (pipes, named pipes, local TCP connections), since
the underlying scheduler mechanisms are the same.
That's not to say that batching inserts doesn't make a difference--it
clearly does, and it would probably be a much larger difference with
actual network round-trips--but round-trips to a local server aren't
inherently slow. I'd be (casually) interested in knowing what the
actual costs are behind an SQL command round-trip (where the command
isn't blocking on I/O, eg. an INSERT inside a transaction, with no I/O
for things like constraint checks that need to be done before the
command can return success).
--
Glenn Maynard
#include stdio.h
#include unistd.h
#include assert.h
main()
{
int parent[2];
int ret = pipe(parent);
assert(ret != -1);
int child[2];
ret = pipe(child);
assert(ret != -1);
int pid = fork();
assert(pid != -1);
if(pid != 0)
{
// parent
close(parent[0]);
close(child[1]);
int wfd = parent[1];
int rfd = child[0];
printf(go\n);
int i = 100;
while(i--)
{
char c = 1;
ret = write(wfd, c, 1);
assert(ret == 1);
ret = read(rfd, c, 1);
assert(ret == 1);
}
printf(done\n);
}
else
{
// child
close(parent[1]);
close(child[0]);
int wfd = child[1];
int rfd = parent[0];
int i = 100;
while(i--)
{
char c = 1;
ret = read(rfd, c, 1);
assert(ret == 1);
ret = write(wfd, c, 1);
assert(ret == 1);
}
}
}
--
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] performance for high-volume log insertion
On Wed, Apr 22, 2009 at 5:51 PM, Stephen Frost sfr...@snowman.net wrote: For a single column table, I wouldn't expect much either. With more columns I think it would be a larger improvement. Maybe. I'm not sure why parsing (1,2,3,4,5) in an EXECUTE parameter should be faster than parsing the exact same thing in an INSERT, though. I've seen it help, but I was sending everything as binary (I figured, once I'm doing it, might as well do it all), which included dates, timestamps, IP addresses, integers, and some text. It may have more of an impact on dates and timestamps than on simple integers. Of course, you still need to get it in that format. Be careful to include any parsing you're doing to create the binary date in the benchmarks. Inevitably, at least part of the difference will be costs simply moving from the psql process to your own. -- 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] Nested query performance issue
On Tue, Apr 14, 2009 at 5:33 AM, Matthew Wakeling matt...@flymine.org wrote: It's about 10% faster for me. I'm surprised the planner can't figure out that this join is redundant. Because the join isn't redundant? You're making the assumption that for every score.game_id there is exactly one game.id that matches. Of course, you may have a unique constraint and foreign key/trigger that ensures this. That's the definition of the tables I gave. CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY); -- pk implies unique CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL, game_id INTEGER REFERENCES game (id)); (I don't think it makes any difference to whether this can be optimized, but adding NOT NULL back to game_id doesn't change it, either.) -- 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] Nested query performance issue
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: CREATE FUNCTION topnscores(game_id int , n int) RETURNS SETOF score LANGUAGE SQL AS $$ SELECT * FROM score s WHERE s.game_id = $1 ORDER BY score DESC LIMIT $2 $$; SELECT (sub.ts).id, (sub.ts).score, (sub.ts).game_id FROM (SELECT topnscores(g.id, 5) ts FROM game g) sub; The inner query: SELECT topnscores(g.id, 5) ts FROM game g http://www.postgresql.org/docs/8.3/static/xfunc-sql.html says this is deprecated (though no deprecation warning is being generated): Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases. It doesn't say how else to write this, though, and it's not obvious to me. SELECT ts.* FROM topnscores(g.id, 5) AS ts, game g doesn't work (function expression in FROM cannot refer to other relations of same query level). Is there an equivalent way to do this so I won't have deprecation looming over my back? I'm likely to become very dependent on this pattern. -- 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] Nested query performance issue
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: SELECT s.* FROM score s, game g WHERE s.game_id = g.id AND s.id IN ( SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score DESC LIMIT 1 ); You don't really need the join with game here, simplifying this into: SELECT s.* FROM score s WHERE s.id IN ( SELECT s2.id FROM score s2 WHERE s2.game_id=s.game_id ORDER BY s2.score DESC LIMIT 1 ); I don't think it makes it any faster, though. It's about 10% faster for me. I'm surprised the planner can't figure out that this join is redundant. SELECT * FROM ( SELECT s.*, rank() OVER (PARTITION BY s.game_id ORDER BY score DESC) AS rank FROM score s ) AS sub WHERE rank = 5; but I'm not sure how much faster it is. At least here on my laptop it does a full index scan on score, which may or may not be faster than just picking the top N values for each game using the index. I'll definitely check this out when 8.4 is released. You can do that approach with a SQL function: CREATE FUNCTION topnscores(game_id int , n int) RETURNS SETOF score LANGUAGE SQL AS $$ SELECT * FROM score s WHERE s.game_id = $1 ORDER BY score DESC LIMIT $2 $$; SELECT (sub.ts).id, (sub.ts).score, (sub.ts).game_id FROM (SELECT topnscores(g.id, 5) ts FROM game g) sub; (as ts, for anyone trying this at home) Thanks--this one runs in 32ms, which seems about right compared against the original fast LIMIT 1 version. I see a slight improvement if I mark the function stable: 31.9ms to 31.2; minor but consistent. Just out of curiosity, any explanations for this difference? I don't see any change in the resulting query plan, but the plan doesn't enter the function call. -- 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] Nested query performance issue
2009/4/9 Віталій Тимчишин tiv...@gmail.com: create or replace function explode_array(in_array anyarray) returns setof anyelement as $$ select ($1)[s] from generate_series(1,array_upper($1, 1)) as s; $$ language sql immutable; I tried using an ARRAY like this, but didn't quite figure out the explode_array piece. -- 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
[PERFORM] Nested query performance issue
(This is related to an earlier post on -sql.) I'm querying for the N high scores for each game, with two tables: scores and games. CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY); CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL, game_id INTEGER REFERENCES game (id)); -- test data: 1000 games, 10 scores INSERT INTO game (id) select generate_series(1,1000); INSERT INTO score (game_id, score) select game.id, random() from game, generate_series(1,100); CREATE INDEX score_idx1 ON score (game_id, score desc); ANALYZE; This query retrieves the single highest score for each game, but doesn't allow any more than that--I can't get the top five scores for each game. However, it's very fast: on the above test data, it runs in 25ms on my system. With 100 scores, it takes 40ms. SELECT s.* FROM score s WHERE s.id IN ( -- Get the high scoring score ID for each game: SELECT ( -- Get the high score for game g: SELECT s2.id FROM score s2 WHERE s2.game_id = g.id ORDER BY s2.score DESC LIMIT 1 ) FROM game g ); This rewrite allows getting the top N scores. Unfortunately, this one takes 950ms for the same data. With 100 scores, it takes 14800ms. SELECT s.* FROM score s, game g WHERE s.game_id = g.id AND s.id IN ( SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score DESC LIMIT 1 ); This seems simple: for each game, search for the highest score, and then scan the tree to get the next N-1 highest scores. The first version does just that, but the second one is doing a seq scan over score. I do want to be able to use a LIMIT higher than 1, which only works with the second form. Any suggestions of how to get the efficient scanning of the first while being able to use a LIMIT greater than 1? (It'd even be faster to make several calls to the first version, varying an OFFSET to get each high score--but that's terrible.) -- 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] Nested query performance issue
(I didn't notice that I ended up with score.score in this test case. Oops.) 2009/4/8 Віталій Тимчишин tiv...@gmail.com: How about select s1.* from score s1 join score s2 on s1.game_id=s2.game_id and s2.score = s1.score group by s1.* having count(s2.*) = N I can see what this is doing, but I'm getting: ERROR: could not identify an ordering operator for type score HINT: Use an explicit ordering operator or modify the query. I'm not sure why; if I replace s1.* and s2.* with s1.id and s2.id it works, but then I only get IDs. Unfortunately, with N = 1 this takes 8100ms (vs. 950ms and 25ms)... -- 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
