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 06B79D1B1D9 for ; Wed, 9 Jun 2004 18:04:20 -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 35885-05 for ; Wed, 9 Jun 2004 21:04:14 +0000 (GMT) Received: from server272.com (ns1.server272.com [64.14.68.49]) by svr1.postgresql.org (Postfix) with SMTP id D2A2CD1B1A5 for ; Wed, 9 Jun 2004 18:04:11 -0300 (ADT) Received: (qmail 6857 invoked from network); 9 Jun 2004 21:05:16 -0000 Received: from unknown (HELO pesky) (63.170.214.187) by server272.com with SMTP; 9 Jun 2004 21:05:16 -0000 Subject: Re: Index oddity From: ken To: Rod Taylor Cc: Postgresql Performance In-Reply-To: <1086814597.2539.92.camel@jester> References: <1086809460.32077.246.camel@pesky> <1086811930.2539.90.camel@jester> <1086814209.32077.252.camel@pesky> <1086814597.2539.92.camel@jester> Content-Type: text/plain Message-Id: <1086814928.32077.259.camel@pesky> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Wed, 09 Jun 2004 14:02:09 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TAG_BALANCE_TABLE X-Spam-Level: X-Archive-Number: 200406/104 X-Sequence-Number: 7162 On Wed, 2004-06-09 at 13:56, Rod Taylor wrote: > On Wed, 2004-06-09 at 16:50, ken wrote: > > Thanks Rod, > > > > This setting has no effect however. If I set statistics to 1000, or > > Okay.. but you never did send EXPLAIN ANALYZE output. I want to know > what it is really finding. Ah, sorry, missed the ANALYZE portion of your request (thought you only wanted the result of explain if it changed due to the setting). Here is the query plan with statistics on diagonalsize set to the default (-1) ... Seq Scan on nrgfeature f (cost=0.00..32176.98 rows=19134 width=218) (actual time=61.640..1009.414 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)) ... 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)) ... so yeah, its obviously finding way, way less rows than it thinks it will. thanks, ken > > > On Wed, 2004-06-09 at 13:12, Rod Taylor wrote: > > > It seems to believe that the number of rows returned for the >49.999 > > > case will be 4 times the number for the >50 case. If that was true, then > > > the sequential scan would be correct. > > > > > > ALTER TABLE ALTER COLUMN diagonalsize SET STATISTICS 1000; > > > ANALZYE
; > > > > > > Send back EXPLAIN ANALYZE output for the >49.999 case. > > >