Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5908337BD74 for ; Wed, 1 Oct 2008 17:05:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90659-09 for ; Wed, 1 Oct 2008 17:05:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by postgresql.org (Postfix) with ESMTP id 5FE0237BCFB for ; Wed, 1 Oct 2008 17:05:33 -0300 (ADT) X-EthosMedia-Virus-Scanned: no infections found Received: from temoku.sf.agliodbs.com (account josh@agliodbs.com [64.81.245.111] verified) by davinci.ethosmedia.com (CommuniGate Pro SMTP 5.2.6) with ESMTPSA id 17785302; Wed, 01 Oct 2008 13:05:53 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: Confusing Query Performance Date: Wed, 1 Oct 2008 13:12:59 -0700 User-Agent: KMail/1.8 Cc: "Gauri Kanekar" References: <7e4ba9550810010334q1ed0e93cn9e84f230a1028a2f@mail.gmail.com> In-Reply-To: <7e4ba9550810010334q1ed0e93cn9e84f230a1028a2f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200810011312.59603.josh@agliodbs.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/7 X-Sequence-Number: 31588 On Wednesday 01 October 2008 03:34, Gauri Kanekar wrote: > =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D186.26..647160.32 rows=3D= 42543 width=3D16) (actual > time=3D655.832..6622.011 rows=3D5120582 loops=3D1) That nested loop estimate is off by 100x, which is why the DB is using a=20 slow nested loop for a large amount of data. I'd try increasing your=20 statistics collection, analyze, and re-run the query. =2D-=20 =2D-Josh Josh Berkus PostgreSQL San Francisco