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 6097CD1B8B7 for ; Tue, 27 Apr 2004 18:55:58 -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 47269-02 for ; Tue, 27 Apr 2004 18:55:58 -0300 (ADT) Received: from 192.168.0.11 (fw.i-trade.hu [212.92.1.62]) by svr1.postgresql.org (Postfix) with SMTP id EB9D3D1B8A5 for ; Tue, 27 Apr 2004 18:55:54 -0300 (ADT) Received: (qmail 32297 invoked from network); 27 Apr 2004 21:35:54 -0000 Received: from unknown (HELO atesz) (212.92.7.130) by 0 with SMTP; 27 Apr 2004 21:35:54 -0000 From: "Atesz" To: "'Vitaly Belman'" , Subject: Re: Simply join in PostrgeSQL takes too long Date: Tue, 27 Apr 2004 23:56:07 +0200 Message-ID: <002a01c42ca2$7492ac30$0b02010a@atesz> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 In-Reply-To: <282909734.20040428002740@012.net.il> 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/467 X-Sequence-Number: 6767 Hi, You can try some variation: SELECT book_id FROM bookgenres, genre_children WHERE bookgenres.genre_id = genre_children.genre_child_id AND genre_children.genre_id = 1 GROUP BY book_id LIMIT 10 The next works if the 'genre_child_id' is UNIQUE on the 'genre_children' table. SELECT book_id FROM bookgenres WHERE bookgenres.genre_id = (SELECT genre_child_id FROM genre_children WHERE genre_id = 1) GROUP BY book_id LIMIT 10 You may need some index. Try these with EXPLAIN! CREATE INDEX bookgenres_genre_id_book_id ON bookgenres(genre_id, book_id); or CREATE INDEX bookgenres_book_id_genre_id ON bookgenres(book_id, genre_id); CREATE INDEX genre_children_genre_id ON genre_children(genre_id); Regards, Antal Attila