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 ED24ED1B211 for ; Wed, 9 Jun 2004 18:29:11 -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 54647-03 for ; Wed, 9 Jun 2004 21:29:05 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5ABDBD1B16B for ; Wed, 9 Jun 2004 18:29:02 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id EA53576A61; Wed, 9 Jun 2004 17:29:08 -0400 (EDT) Subject: Re: Index oddity From: Rod Taylor To: ken Cc: Postgresql Performance In-Reply-To: <1086814928.32077.259.camel@pesky> References: <1086809460.32077.246.camel@pesky> <1086811930.2539.90.camel@jester> <1086814209.32077.252.camel@pesky> <1086814597.2539.92.camel@jester> <1086814928.32077.259.camel@pesky> Content-Type: text/plain Message-Id: <1086816543.2539.98.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 09 Jun 2004 17:29:03 -0400 Content-Transfer-Encoding: 7bit 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: 200406/105 X-Sequence-Number: 7163 > ... and here is the plan with statistics set to 1000 ... > > Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218) > (actual time=63.544..1002.701 rows=225 loops=1) > Filter: ((upperrightx > 321264.236977215::double precision) AND > (lowerleftx < 324046.799812083::double precision) AND (upperrighty > > 123286.261898636::double precision) AND (lowerlefty < > 124985.927450476::double precision) AND (diagonalsize > 49.999::double > precision)) It's better like this, but still way off the mark. Even your good query which uses the index was out by more than an order of magnitude. Try raising the statistics levels for upperrightx, lowerleftx, upperrighty and lowerlefty. Failing that, you might be able to push it back down again by giving diagonalsize an upper limit. Perhaps 500 is a value that would never occur. AND (diagonalsize BETWEEN 49.999::double precision AND 500)