Re: Statistics use with functions

Список
Период
Сортировка
От Tom Lane
Тема Re: Statistics use with functions
Дата
Msg-id 7698.1241797685@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Statistics use with functions  (Matthew Wakeling)
Ответы Re: Statistics use with functions  (Matthew Wakeling)
Список pgsql-performance
Дерево обсуждения
Statistics use with functions  (Matthew Wakeling, )
 Re: Statistics use with functions  (Tom Lane, )
  Re: Statistics use with functions  (Matthew Wakeling, )
   Re: Statistics use with functions  (Tom Lane, )
Matthew Wakeling <> writes:
> When I wrap the fields in the constraints in a LOWER() function, the
> planner stops looking at the statistics and makes a wild guess, even
> though it is very obvious from just looking what the result should be.

Well, in general the planner can't assume anything about the statistics
of a function result, since it doesn't know how the function behaves.
In this case, however, you evidently have an index on lower(distance)
which should have caused ANALYZE to gather stats on the values of that
functional expression.  It looks like there might be something wrong
there --- can you look into pg_stats and see if there is such an entry
and if it looks sane?

> Also, why does the BitmapAnd say zero actual rows?

There isn't any reasonably-inexpensive way for EXPLAIN ANALYZE to
determine how many rows are represented by a bitmap result, so it
doesn't try.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Transparent table partitioning in future version of PG?
Следующее
От: Paolo Rizzi
Дата:
Сообщение: Re: PostgreSQL with PostGIS on embedded hardware