Re: Statistics use with functions

From: Tom Lane
Subject: Re: Statistics use with functions
Date: ,
Msg-id: 7698.1241797685@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Statistics use with functions  (Matthew Wakeling)
Responses: Re: Statistics use with functions  (Matthew Wakeling)
List: pgsql-performance

Tree view

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 by date:

From: Tom Lane
Date:
Subject: Re: Transparent table partitioning in future version of PG?
From: Paolo Rizzi
Date:
Subject: Re: PostgreSQL with PostGIS on embedded hardware