Re: [PERFORM] Odd sorting behaviour
On Tue, Jul 20, 2004 at 10:18:19PM -0400, Rod Taylor wrote: I've taken a look and managed to cut out quite a bit of used time. You'll need to confirm it's the same results though (I didn't -- it is the same number of results (query below) It looks very much like the same results. Secondly, I had no luck getting the hashjoin but this probably doesn't matter. I've assumed that the number of users will climb faster than the product set offered, and generated additional data via the below command run 4 times: Actually, the number of users won't climb that much faster; what will probably increase is the number of opinions. I found that by this point, the hashjoin and mergejoin have essentially the same performance -- in otherwords, as you grow you'll want the mergejoin eventually so I wouldn't worry about it too much. Hm, OK. -- Plain join okay since o12.correlation 0 -- eliminates any NULLs anyway. -- Was RIGHT JOIN OK, that makes sense (although I don't really see why it should be faster). -- Was old Left join WHERE o3.prodid NOT IN (SELECT prodid FROM opinions AS o4 WHERE uid = 1355) As my server is 7.2 and not 7.4, that obviously won't help much :-) Thanks anyway, though -- we'll upgrade eventually, and it'll help then. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 15, 2004 at 02:08:54PM +0200, Steinar H. Gunderson wrote: sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) I've tried some further tweaking, but I'm still unable to force it into doing a hash join -- any ideas how I can find out why it chooses a merge join? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Odd sorting behaviour
Steinar, I've tried some further tweaking, but I'm still unable to force it into doing a hash join -- any ideas how I can find out why it chooses a merge join? I'm sorry, I can't really give your issue the attention it deserves. At this point, I'd have to get a copy of your database, and play around with alternate query structures; and I don't have time. Sorry! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Odd sorting behaviour
Steinar, I've tried some further tweaking, but I'm still unable to force it into doing a hash join -- any ideas how I can find out why it chooses a merge join? Actually, quick question -- have you tried setting enable_mergjoin=false to see the plan the system comes up with? Is it in fact faster? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Odd sorting behaviour
I could of course post the updated query plan if anybody is interested; let me know. (The data is still available if anybody needs it as well, of course.) I've taken a look and managed to cut out quite a bit of used time. You'll need to confirm it's the same results though (I didn't -- it is the same number of results (query below) First off, DROP INDEX prodid_index;. It doesn't help anything since the primary key is just as usable, but it does take enough space that it causes thrashing in the buffer_cache. Any queries based on prodid will use the index for the PRIMARY KEY instead. Secondly, I had no luck getting the hashjoin but this probably doesn't matter. I've assumed that the number of users will climb faster than the product set offered, and generated additional data via the below command run 4 times: INSERT INTO opinions SELECT prodid, uid + (SELECT max(uid) FROM opinions), opinion FROM opinions; I found that by this point, the hashjoin and mergejoin have essentially the same performance -- in otherwords, as you grow you'll want the mergejoin eventually so I wouldn't worry about it too much. New Query cuts about 1/3rd the time, forcing hashjoin gets another 1/3rd but see the above note: SELECT o3.prodid , SUM(o3.opinion*o12.correlation) AS total_correlation FROM opinions o3 -- Plain join okay since o12.correlation 0 -- eliminates any NULLs anyway. -- Was RIGHT JOIN JOIN (SELECT o2.uid , SUM(o1.opinion*o2.opinion)/SQRT(count(*)::numeric) AS correlation FROM opinions AS o1 JOIN opinions AS o2 USING (prodid) WHERE o1.uid = 1355 GROUP BY o2.uid ) AS o12 USING (uid) -- Was old Left join WHERE o3.prodid NOT IN (SELECT prodid FROM opinions AS o4 WHERE uid = 1355) AND o3.opinion 0 AND o12.correlation 0 GROUP BY o3.prodid ORDER BY total_correlation desc; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 15, 2004 at 12:52:38AM -0400, Tom Lane wrote: No, it's not missing anything. The number being reported here is the number of rows pulled from the plan node --- but this plan node is on the inside of a merge join, and one of the properties of merge join is that it will do partial rescans of its inner input in the presence of equal keys in the outer input. If you have, say, 10 occurrences of 42 in the outer input, then any 42 rows in the inner input have to be rescanned 10 times. EXPLAIN ANALYZE will count each of them as 10 rows returned by the input node. OK, that makes sense, although it seems to me as is loops= should have been something larger than 1 if the data was scanned multiple times. The large multiple here (80-to-one overscan) says that you've got a lot of duplicate values in the outer input. This is generally a good situation to *not* use a mergejoin in ;-). We do have some logic in the planner that attempts to estimate the extra cost involved in such rescanning, but I'm not sure how accurate the cost model is. Hum, I'm not sure if I'm in the termiology here -- outer input in A left join B is A, right? But yes, I do have a lot of duplicates, that seems to match my data well. Raising shared_buffers seems unlikely to help. I do agree with raising sort_mem --- not so much to make the merge faster as to encourage the thing to try a hash join instead. sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Odd sorting behaviour
Steinar, sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) Well, then the next step is increasing the statistical sampling on the 3 join columns in that table. Try setting statistics to 500 for each of the 3 cols, analyze, and see if that makes a difference. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 15, 2004 at 11:11:33AM -0700, Josh Berkus wrote: sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) Well, then the next step is increasing the statistical sampling on the 3 join columns in that table. Try setting statistics to 500 for each of the 3 cols, analyze, and see if that makes a difference. Made no difference on either version (7.2 or 7.4). BTW, you guys can stop Cc-ing me now; I'm subscribed. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 08, 2004 at 12:19:13PM +0200, Steinar H. Gunderson wrote: I'm trying to find out why one of my queries is so slow -- I'm primarily using PostgreSQL 7.2 (Debian stable), but I don't really get much better performance with 7.4 (Debian unstable). My prototype table looks like this: I hate to nag, but it's been a week with no reply; did anybody look at this? Is there any more information I can supply to make it easier? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Odd sorting behaviour
Steinar, - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? And why would it take ~100ms to sort the rows at all? (In earlier tests, this was _one full second_ but somehow that seems to have improved, yet without really improving the overall query time. I'm puzzled by the 83792 rows as well. I've a feeling that Explain Analyze is failing to output a step. - Why does it use uid_index for an index scan on the table, when it obviously has no filter on it (since it returns all the rows)? In order to support the merge join. It should be a bit faster to do the sort using the index than the actual table. Also, because you pass the 0 condition. Furthermore, why would this take half a second? (The machine is a 950MHz machine with SCSI disks.) I don't see half a second here. - Also, the outer sort (the sorting of the 58792 rows from the merge join) is slow. :-) I don't see a sort after the merge join. Which version are we talking about? I'm looking at the 7.4 version because that outputs more detail. Most of your time is spent in that merge join. Why don't you try doubling sort_mem temporarily to see how it does? Or even raising shared_buffers? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
Josh Berkus [EMAIL PROTECTED] writes: - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? I'm puzzled by the 83792 rows as well. I've a feeling that Explain Analyze is failing to output a step. No, it's not missing anything. The number being reported here is the number of rows pulled from the plan node --- but this plan node is on the inside of a merge join, and one of the properties of merge join is that it will do partial rescans of its inner input in the presence of equal keys in the outer input. If you have, say, 10 occurrences of 42 in the outer input, then any 42 rows in the inner input have to be rescanned 10 times. EXPLAIN ANALYZE will count each of them as 10 rows returned by the input node. The large multiple here (80-to-one overscan) says that you've got a lot of duplicate values in the outer input. This is generally a good situation to *not* use a mergejoin in ;-). We do have some logic in the planner that attempts to estimate the extra cost involved in such rescanning, but I'm not sure how accurate the cost model is. Most of your time is spent in that merge join. Why don't you try doubling sort_mem temporarily to see how it does? Or even raising shared_buffers? Raising shared_buffers seems unlikely to help. I do agree with raising sort_mem --- not so much to make the merge faster as to encourage the thing to try a hash join instead. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Odd sorting behaviour
[Apologies if this reaches the list twice -- I sent a copy before subscribing, but it seems to be stuck waiting for listmaster forever, so I subscribed and sent it again.] Hi, I'm trying to find out why one of my queries is so slow -- I'm primarily using PostgreSQL 7.2 (Debian stable), but I don't really get much better performance with 7.4 (Debian unstable). My prototype table looks like this: CREATE TABLE opinions ( prodid INTEGER NOT NULL, uid INTEGER NOT NULL, opinion INTEGER NOT NULL, PRIMARY KEY ( prodid, uid ) ); In addition, there are separate indexes on prodid and uid. I've run VACUUM ANALYZE before all queries, and they are repeatable. (If anybody needs the data, that could be arranged -- it's not secret or anything :-) ) My query looks like this: EXPLAIN ANALYZE SELECT o3.prodid, SUM(o3.opinion*o12.correlation) AS total_correlation FROM opinions o3 RIGHT JOIN ( SELECT o2.uid, SUM(o1.opinion*o2.opinion)/SQRT(count(*)+0.0) AS correlation FROM opinions o1 LEFT JOIN opinions o2 ON o1.prodid=o2.prodid WHERE o1.uid=1355 GROUP BY o2.uid ) o12 ON o3.uid=o12.uid LEFT JOIN ( SELECT o4.prodid, COUNT(*) as num_my_comments FROM opinions o4 WHERE o4.uid=1355 GROUP BY o4.prodid ) nmc ON o3.prodid=nmc.prodid WHERE nmc.num_my_comments IS NULL AND o3.opinion0 AND o12.correlation0 GROUP BY o3.prodid ORDER BY total_correlation desc; And produces the query plan at http://www.samfundet.no/~sesse/queryplan.txt (The lines were a bit too long to include in an e-mail :-) ) Note that the o3.opinion0 AND o12.correleation0 lines are an optimization; I can run the query fine without them and it will produce the same results, but it goes slower both in 7.2 and 7.4. There are a few oddities here: - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? And why would it take ~100ms to sort the rows at all? (In earlier tests, this was _one full second_ but somehow that seems to have improved, yet without really improving the overall query time. shared_buffers is 4096 and sort_mem is 16384, so it should really fit into RAM.) - Why does it use uid_index for an index scan on the table, when it obviously has no filter on it (since it returns all the rows)? Furthermore, why would this take half a second? (The machine is a 950MHz machine with SCSI disks.) - Also, the outer sort (the sorting of the 58792 rows from the merge join) is slow. :-) 7.4 isn't really much better: http://www.samfundet.no/~sesse/queryplan74.txt Note that this is run on a machine with almost twice the speed (in terms of CPU speed, at least). The same oddities are mostly present (such as o12 returning 1186 rows, but 58788 rows are sorted), so I really don't understand what's going on here. Any ideas on how to improve this? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Odd sorting behaviour
[Please CC me on all replies, I'm not subscribed to this list] Hi, I'm trying to find out why one of my queries is so slow -- I'm primarily using PostgreSQL 7.2 (Debian stable), but I don't really get much better performance with 7.4 (Debian unstable). My prototype table looks like this: CREATE TABLE opinions ( prodid INTEGER NOT NULL, uid INTEGER NOT NULL, opinion INTEGER NOT NULL, PRIMARY KEY ( prodid, uid ) ); In addition, there are separate indexes on prodid and uid. I've run VACUUM ANALYZE before all queries, and they are repeatable. (If anybody needs the data, that could be arranged -- it's not secret or anything :-) ) My query looks like this: EXPLAIN ANALYZE SELECT o3.prodid, SUM(o3.opinion*o12.correlation) AS total_correlation FROM opinions o3 RIGHT JOIN ( SELECT o2.uid, SUM(o1.opinion*o2.opinion)/SQRT(count(*)+0.0) AS correlation FROM opinions o1 LEFT JOIN opinions o2 ON o1.prodid=o2.prodid WHERE o1.uid=1355 GROUP BY o2.uid ) o12 ON o3.uid=o12.uid LEFT JOIN ( SELECT o4.prodid, COUNT(*) as num_my_comments FROM opinions o4 WHERE o4.uid=1355 GROUP BY o4.prodid ) nmc ON o3.prodid=nmc.prodid WHERE nmc.num_my_comments IS NULL AND o3.opinion0 AND o12.correlation0 GROUP BY o3.prodid ORDER BY total_correlation desc; And produces the query plan at http://www.samfundet.no/~sesse/queryplan.txt (The lines were a bit too long to include in an e-mail :-) ) Note that the o3.opinion0 AND o12.correleation0 lines are an optimization; I can run the query fine without them and it will produce the same results, but it goes slower both in 7.2 and 7.4. There are a few oddities here: - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? And why would it take ~100ms to sort the rows at all? (In earlier tests, this was _one full second_ but somehow that seems to have improved, yet without really improving the overall query time. shared_buffers is 4096 and sort_mem is 16384, so it should really fit into RAM.) - Why does it use uid_index for an index scan on the table, when it obviously has no filter on it (since it returns all the rows)? Furthermore, why would this take half a second? (The machine is a 950MHz machine with SCSI disks.) - Also, the outer sort (the sorting of the 58792 rows from the merge join) is slow. :-) 7.4 isn't really much better: http://www.samfundet.no/~sesse/queryplan74.txt Note that this is run on a machine with almost twice the speed (in terms of CPU speed, at least). The same oddities are mostly present (such as o12 returning 1186 rows, but 58788 rows are sorted), so I really don't understand what's going on here. Any ideas on how to improve this? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
