От: Rod Taylor
Тема: Re: Index oddity
Дата: ,
Msg-id: 1086816543.2539.98.camel@jester
(см: обсуждение, исходный текст)
Ответ на: Re: Index oddity  (ken)
Ответы: Re: Index oddity  (ken)
Список: 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, )

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




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

От: Mark Kirkwood
Дата:
Сообщение: Re: Index oddity
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Index oddity