Re: Expression index ignores column statistics target

On Fri, Sep 30, 2005 at 11:59:26PM -0400, Bruce Momjian wrote:
> This is expected. The main TODO items is:
>
> * Allow accurate statistics to be collected on indexes with more than
> one column or expression indexes, perhaps using per-index statistics
>
> Basically, we don't have multi-column or expression statistics. ANALYZE
> just analyzes columns, even if an expression index exists.

But the row count estimates imply that expression index queries do
use column statistics, presumably as a proxy in the absence of
expression statistics. This looks like a relevant commit:

http://archives.postgresql.org/pgsql-committers/2004-02/msg00124.php

The behavior I observed is that the planner does appear to use
column statistics when planning an expression index query, but it
doesn't appear to honor a column's non-default statistics target.
In other words:

* Row count estimates for expression index queries (at least simple
ones) are reasonably accurate for the N most common column values,
where N is the value of default_statistics_target when ANALYZE
was run.

* Specifically setting the column's statistics target with ALTER
TABLE SET STATISTICS doesn't result in better statistics for
expression index queries.

That difference in behavior seems odd: if default_statistics_target
has an effect, why doesn't ALTER TABLE SET STATISTICS?

--
Michael Fuhr