Skip site navigation (1) Skip section navigation (2)

Re: Index oddity

From: ken <southerland(at)samsixedd(dot)com>
To: Rod Taylor <pg(at)rbt(dot)ca>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Index oddity
Date: 2004-06-09 21:02:09
Message-ID: 1086814928.32077.259.camel@pesky (view raw or flat)
Thread:
Lists: pgsql-performance
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.
> > > 



In response to

Responses

pgsql-performance by date

Next:From: Rod TaylorDate: 2004-06-09 21:29:03
Subject: Re: Index oddity
Previous:From: Rod TaylorDate: 2004-06-09 20:56:38
Subject: Re: Index oddity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group