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
columnvalues, 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
statisticsfor 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