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 C2DDAD1B4DF for ; Tue, 27 Apr 2004 19:01:53 -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 48376-05 for ; Tue, 27 Apr 2004 19:01:47 -0300 (ADT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9FB2AD1BC54 for ; Tue, 27 Apr 2004 19:01:43 -0300 (ADT) Received: from [134.22.68.249] (dyn-68-249.tor.dsl.tht.net [134.22.68.249]) by tht.net (Postfix) with ESMTP id 99FD276A7A; Tue, 27 Apr 2004 18:01:50 -0400 (EDT) Subject: Re: Simply join in PostrgeSQL takes too long From: Rod Taylor To: Vitaly Belman Cc: Postgresql Performance In-Reply-To: <282909734.20040428002740@012.net.il> References: <282909734.20040428002740@012.net.il> Content-Type: text/plain Message-Id: <1083103293.30065.100.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Tue, 27 Apr 2004 18:01:34 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/469 X-Sequence-Number: 6769 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 You're taking the wrong approach. Rather than using a select query to ensure that the book_id is distinct, add a constraint to the table so that is guaranteed. CREATE UNIQUE INDEX bv_bookgeneres_unq ON bv_bookgenres(book_id, genre_id); Now you can do a simple join (Drop the DISTINCT keyword) and achieve the same results. The point is that a book cannot be of a certain genre more than once. Without the distinct, this should take a matter of a few milliseconds to execute.