Re: Setting Statistics on Functional Indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux