Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 124E79FB7BE for ; Wed, 20 Jun 2007 13:53:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 60187-01 for ; Wed, 20 Jun 2007 13:53:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mtai03.charter.net (mtai03.charter.net [209.225.8.183]) by postgresql.org (Postfix) with ESMTP id 283A39FB5D3 for ; Wed, 20 Jun 2007 13:53:53 -0300 (ADT) Received: from aa03.charter.net ([10.20.200.155]) by mtai03.charter.net (InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP id <20070620165348.FWDP1549.mtai03.charter.net@aa03.charter.net>; Wed, 20 Jun 2007 12:53:48 -0400 Received: from [10.0.1.2] (really [66.188.164.45]) by aa03.charter.net with ESMTP id <20070620165348.WKZT2422.aa03.charter.net@[10.0.1.2]>; Wed, 20 Jun 2007 12:53:48 -0400 In-Reply-To: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com> References: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Message-Id: Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable From: Michael Glaesemann Subject: Re: Slow indexscan Date: Wed, 20 Jun 2007 11:53:44 -0500 To: Mikko Partio X-Mailer: Apple Mail (2.752.2) X-Chzlrs: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/427 X-Sequence-Number: 25460 On Jun 20, 2007, at 9:02 , Mikko Partio wrote: > Relevant facts: > pg version 7.3.4 (yeah very old, we are upgrading asap) There have been many performance improvements=97not to mention security =20= and data-eating bug fixes=97since then. Upgrading should be one of your =20= highest priorities. And it may even fix the issue at hand! > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > (cost=3D0.00..365.13rows=3D13 width=3D137) (actual time=3D > 120.83..10752.64 rows=3D539 loops=3D1) Something appears a bit off with your index, or at least the =20 statistics Postgres is using to estimate it. It's estimating that the =20= query will return 13 rows, but you're actually returning 539. Maybe =20 there's some corruption in the index which is leading to both the =20 performance issue you're seeing and the statistics issues. Have you =20 tried REINDEX? Michael Glaesemann grzm seespotcode net