X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0404FD1B2E0 for ; Tue, 20 Jul 2004 23:21:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49517-03 for ; Wed, 21 Jul 2004 02:21:33 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id BB2D9D1B20D for ; Tue, 20 Jul 2004 23:20:54 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id D0E8B76A7F; Tue, 20 Jul 2004 22:18:28 -0400 (EDT) Subject: Re: Odd sorting behaviour From: Rod Taylor To: "Steinar H. Gunderson" Cc: Postgresql Performance In-Reply-To: <20040720172100.GA22262@uio.no> References: <20040708101913.GA15871@uio.no> <20040715120854.GA31259@uio.no> <20040720115116.GB19633@uio.no> <200407201006.08649.josh@agliodbs.com> <20040720172100.GA22262@uio.no> Content-Type: text/plain Message-Id: <1090376297.12565.9.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Tue, 20 Jul 2004 22:18:19 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/139 X-Sequence-Number: 7530 > 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;