Re: Setting Statistics on Functional Indexes

Shaun Thomas <sthomas(at)optionshouse(dot)com> writes:
> 1. Is there any way to specifically set stats on a functional index?

Sure, the same way you would for a table.

regression=# create table foo (f1 int, f2 int);
CREATE TABLE
regression=# create index fooi on foo ((f1 + f2));
CREATE INDEX
regression=# \d fooi
Index "public.fooi"
Column | Type | Definition
--------+---------+------------
expr | integer | (f1 + f2)
btree, for table "public.foo"

regression=# alter index fooi alter column expr set statistics 5000;
ALTER INDEX

The weak spot in this, and the reason this isn't "officially" supported,
is that the column name for an index expression isn't set in stone.
But as long as you check what it's called you can set its target.

> 2. Why is the planner so ridiculously optimistic with functional
> indexes, even in the case of much higher selectivity as reported by
> pg_stats on the named columns?

It's not particularly (not that you've even defined what you think
"optimistic" is, much less mentioned what baseline you're comparing to).
I tried your example on HEAD and I got what seemed pretty decent
rowcount estimates ...

regards, tom lane