X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 53291D1B8AA for ; Mon, 25 Aug 2003 13:33:50 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 69448-03 for ; Mon, 25 Aug 2003 13:33:19 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 56F91D1B89F for ; Mon, 25 Aug 2003 13:33:17 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h7PGavVO016763 for ; Mon, 25 Aug 2003 22:06:57 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h7PGavQb016750 for ; Mon, 25 Aug 2003 22:06:57 +0530 From: "Shridhar Daithankar" To: PostgreSQL Performance Date: Mon, 25 Aug 2003 22:05:05 +0530 MIME-Version: 1.0 Subject: Re: Query too slow Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3F4A8811.17912.54E46A0@localhost> In-reply-to: <20030825083343.H90125-100000@megazone.bigpanda.com> References: <1061823792.3f4a253015fdc@sistemica.info> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/327 X-Sequence-Number: 3143 On 25 Aug 2003 at 8:44, Stephan Szabo wrote: > On Mon, 25 Aug 2003, Rhaoni Chiu Pereira wrote: > > > Hi List, > > > > As I said before, I'm not a DBA " yet" , but I'm learning ... and I > > already have a PostgreSQL running, so I have to ask some help... > > I got a SQL as folows : > > ... > > Looking at the explain: > > It's choosing lots of nested loops because it's expecting a small number > of rows to be returned at each step but in reality there are alot of rows > so that's may not really be a good choice. > > For example the scan of ftnfco00 is expected to return 295 rows but > actually returns 9339, and it looks like it's not estimating the number of > matches between the tables very well either since the real count gets up > to 240000 in a step where the estimated rows goes to 1. > > What does explain analyze give after set enable_nestloop=off;? In addition to that if it is getting the stats wrong, does running vacuum analyze help? If stats are updated, it should pick up proper plans, right? Bye Shridhar -- Flon's Law: There is not now, and never will be, a language in which it is the least bit difficult to write bad programs.