Re: Statistics use with functions - Mailing list pgsql-performance

From Tom Lane
Subject Re: Statistics use with functions
Date
Msg-id 7698.1241797685@sss.pgh.pa.us
Whole thread Raw
In response to Statistics use with functions  (Matthew Wakeling <matthew@flymine.org>)
Responses Re: Statistics use with functions  (Matthew Wakeling <matthew@flymine.org>)
List pgsql-performance
Matthew Wakeling <matthew@flymine.org> 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:

Previous
From: Matthew Wakeling
Date:
Subject: Statistics use with functions
Next
From: Matthew Wakeling
Date:
Subject: Re: Statistics use with functions