Re: Index oddity (still)

От: Tom Lane
Тема: Re: Index oddity (still)
Дата: ,
Msg-id: 735.1087307526@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Index oddity (still)  (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, )

ken <> writes:
> Is there something wrong with rtree indexes on box data types?

Not per se, but the selectivity estimator for && is just a stub :-(.
Feel free to take a stab at improving this, or take a look at PostGIS
--- I think those guys are working the same problem even now.

> However, even if I *drop* the rtree index on the boundingbox
> column, so that it can't use that index, the optimizer does not choose
> the other index.  Instead it reverts to doing a sequential scan of the
> entire table and its really slow.

This is also a lack-of-statistical-knowledge problem.  The optimizer has
no stats about the distribution of length(lseg(boundingbox)) and so it
does not realize that it'd be reasonable to use that index for a query
like "length(lseg(boundingbox)) > largevalue".  As of 7.5 this issue
will be addressed, but in existing releases I think you could only get
that index to be used by forcing it with enable_seqscan = off :-(

As a short-term workaround it might be reasonable to store that length
as an independent table column, which you could put an index on.  You
could use triggers to ensure that it always matches the boundingbox.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??
От: Frank van Vugt
Дата:
Сообщение: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??