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 3BE085E470C for ; Tue, 31 Aug 2004 17:14:05 -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 43878-10 for ; Tue, 31 Aug 2004 20:13:59 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 09EA65E46CD for ; Tue, 31 Aug 2004 17:13:58 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7VKDwFK014631; Tue, 31 Aug 2004 16:13:58 -0400 (EDT) To: Jean-Max Reymond Cc: pgsql-performance@postgresql.org Subject: Re: Optimizing a request In-reply-to: <4b09a0c0408311159fa91802@mail.gmail.com> References: <4b09a0c0408311159fa91802@mail.gmail.com> Comments: In-reply-to Jean-Max Reymond message dated "Tue, 31 Aug 2004 20:59:11 +0200" Date: Tue, 31 Aug 2004 16:13:58 -0400 Message-ID: <14630.1093983238@sss.pgh.pa.us> From: Tom Lane 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: 200408/482 X-Sequence-Number: 8118 Jean-Max Reymond writes: > explain SELECT art_id, art_titre, art_texte, rub_titre > FROM article inner join rubrique on article.rub_id = rubrique.rub_id > where rub_parent = 8; > Hash Join (cost=8.27..265637.59 rows=25 width=130) > Hash Cond: ("outer".rub_id = "inner".rub_id) > -> Seq Scan on article (cost=0.00..215629.00 rows=10000000 width=108) > -> Hash (cost=8.26..8.26 rows=3 width=22) > -> Index Scan using rubrique_parent on rubrique > (cost=0.00..8.26 rows=3 width=22) > Index Cond: (rub_parent = 8) That seems like a very strange plan choice given those estimated row counts. I'd have expected it to use a nestloop with inner index scan on article_rub_id_index. You haven't done anything odd like disable nestloop, have you? What plan do you get if you turn off enable_hashjoin? (If it's a merge join, then turn off enable_mergejoin and try again.) Also, could we see EXPLAIN ANALYZE not just EXPLAIN output for all these cases? regards, tom lane