pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ken <southerland@samsixedd.com>
To: Rod Taylor <pg@rbt.ca>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Index oddity
Date: Wed, 09 Jun 2004 14:02:09 -0700
Message-ID: <1086814928.32077.259.camel@pesky> (raw)
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>

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 <table> ALTER COLUMN diagonalsize SET STATISTICS 1000;
> > > ANALZYE <table>;
> > > 
> > > Send back EXPLAIN ANALYZE output for the >49.999 case.
> > > 





view thread (12+ messages)  latest in thread

Message-ID: <1086814928.32077.259.camel@pesky>
Permalink:  ../1086814928.32077.259.camel@pesky/
Also on:    postgresql.org/message-id/1086814928.32077.259.camel@pesky

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: southerland@samsixedd.com, pg@rbt.ca
  Subject: Re: Index oddity
  In-Reply-To: <1086814928.32077.259.camel@pesky>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox