X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 36EE5D1EEAC for ; Thu, 29 Apr 2004 18:09:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84785-01 for ; Thu, 29 Apr 2004 18:09:04 -0300 (ADT) Received: from fep12.012.net.il (fep12.012.net.il [212.117.129.243]) by svr1.postgresql.org (Postfix) with ESMTP id 56FC9D1EDB9 for ; Thu, 29 Apr 2004 18:09:01 -0300 (ADT) Received: from 80.178.84.42.forward.012.net.il ([80.178.84.42]) by fep12.012.net.il with ESMTP id <20040429210945.DJKT26710.fep12@80.178.84.42.forward.012.net.il>; Fri, 30 Apr 2004 00:09:45 +0300 Date: Fri, 30 Apr 2004 00:09:36 +0300 From: Vitaly Belman Reply-To: Vitaly Belman X-Priority: 3 (Normal) Message-ID: <147254626500.20040430000936@012.net.il> To: Manfred Koizar Cc: Rod Taylor , Postgresql Performance Subject: Re: Simply join in PostrgeSQL takes too long In-Reply-To: References: <282909734.20040428002740@012.net.il> <1083103293.30065.100.camel@jester> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/505 X-Sequence-Number: 6805 Hello Manfred, I thank everyone for helping me on this - So many tips. I am in the middle of going through them all, till now disabling the enable_mergejoin really helped. Also, I agree that the design might be flawed (I could use triggers and stuff like that) but for now I am just comparing how my project will run on PostgreSQL (Considering migration from MySQL). I'll be reporting back on how the other stuff helped. Regards, Vitaly Belman ICQ: 1912453 AIM: VitalyB1984 MSN: tmdagent@hotmail.com Yahoo!: VitalyBe Wednesday, April 28, 2004, 11:24:41 AM, you wrote: MK> On Tue, 27 Apr 2004 18:01:34 -0400, Rod Taylor wrote: >>On Tue, 2004-04-27 at 17:27, Vitaly Belman wrote: >>> Hello pgsql-performance, >>> >>> I discussed the whole subject for some time in DevShed and didn't >>> achieve much (as for results). I wonder if any of you guys can help >>> out: >>> >>> http://forums.devshed.com/t136202/s.html >>The point is that a book cannot be of a certain genre more than once. MK> Rod, he has a hierarchy of genres. Genre 1 has 6379 child genres and a MK> book can be in more than one of these. MK> Vitaly, though LIMIT makes this look like a small query, DISTINCT MK> requires the whole result set to be retrieved. 0.7 seconds doesn't look MK> so bad for several thousand rows. Did you try with other genre_ids? MK> Maybe a merge join is not the best choice. Set enable_mergejoin to MK> false and see whether you get a (hopefully faster) hash join, assuming MK> that sort_mem is large enough to keep the hash table in memory. MK> If you send me your table contents I'll try it on Linux. MK> Servus MK> Manfred