От: ken
Тема: Re: Index oddity
Дата: ,
Msg-id: 1086814928.32077.259.camel@pesky
(см: обсуждение, исходный текст)
Ответ на: Re: Index oddity  (Rod Taylor)
Ответы: Re: Index oddity  (Rod Taylor)
Re: Index oddity  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Index oddity  (ken, )
 Re: Index oddity  (Rod Taylor, )
  Re: Index oddity  (ken, )
   Re: Index oddity  (Rod Taylor, )
    Re: Index oddity  (ken, )
     Re: Index oddity  (Rod Taylor, )
      Re: Index oddity  (ken, )
       Re: Index oddity  (Christopher Kings-Lynne, )
        Re: Index oddity  (Rod Taylor, )
         Re: Index oddity  (Mark Kirkwood, )
          Re: Index oddity  ("Joshua D. Drake", )
     Re: Index oddity  (Tom Lane, )
      Re: Index oddity (still)  (ken, )
       Re: Index oddity (still)  (Tom Lane, )

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.
> > >




В списке pgsql-performance по дате сообщения:

От: Rod Taylor
Дата:
Сообщение: Re: Index oddity
От: Mark Kirkwood
Дата:
Сообщение: Re: Index oddity