Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1A4639FB42E for ; Thu, 11 Oct 2007 00:16:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 37538-02 for ; Thu, 11 Oct 2007 00:16:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wdscowa01.sc.wdc.com (unknown [129.253.170.98]) by postgresql.org (Postfix) with ESMTP id D2F0D9FB593 for ; Thu, 11 Oct 2007 00:16:36 -0300 (ADT) Received: from wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) by wdscowa01.sc.wdc.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 10 Oct 2007 20:16:21 -0700 Received: from 129.253.108.31 ([129.253.108.31]) by wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) with Microsoft Exchange Server HTTP-DAV ; Thu, 11 Oct 2007 03:16:17 +0000 Received: from neuromancer by 129.253.105.55; 11 Oct 2007 11:16:23 +0800 Subject: Re: Query taking too long. Problem reading explain output. From: Ow Mun Heng To: Alvaro Herrera Cc: Henrik , Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <20071004123038.GA6176@alvh.no-ip.org> References: <15793.1191420918@sss.pgh.pa.us> <2E2D02AB-123A-474D-A197-91A61DC64570@mac.se> <20071004123038.GA6176@alvh.no-ip.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Thu, 11 Oct 2007 11:16:23 +0800 Message-Id: <1192072583.22992.33.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 X-OriginalArrivalTime: 11 Oct 2007 03:16:21.0682 (UTC) FILETIME=[19054120:01C80BB5] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.301 tagged_above=0 required=5 tests=AWL=-0.681, BAYES_40=-0.185, RCVD_NUMERIC_HELO=2.067, RDNS_NONE=0.1 X-Spam-Level: * X-Archive-Number: 200710/122 X-Sequence-Number: 27144 On Thu, 2007-10-04 at 08:30 -0400, Alvaro Herrera wrote: > Henrik wrote: > > > Ahh I had exactly 8 joins. > > Following your suggestion I raised the join_collapse_limit from 8 to 10 and > > the planners decision sure changed but now I have some crazy nested loops. > > Maybe I have some statistics wrong? > > Yeah. The problematic misestimation is exactly the innermost indexscan, > which is wrong by two orders of magnitude: Nested Loops are evil.. and I've no clue on why PG has such big mis-estimates. Mine are like 1:500 I've solved mine using SRFs