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