From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@optionshouse.com
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Setting Statistics on Functional Indexes
In-reply-to: <50881D73.100@optionshouse.com>
References: <50881D73.100@optionshouse.com>
Comments: In-reply-to Shaun Thomas <sthomas@optionshouse.com>
	message dated "Wed, 24 Oct 2012 11:55:15 -0500"
Date: Wed, 24 Oct 2012 15:11:48 -0400
Message-ID: <6668.1351105908@sss.pgh.pa.us>

Shaun Thomas <sthomas@optionshouse.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