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
Date: Wed, 24 Oct 2012 15:11:48 -0400
Message-ID: <6668.1351105908@sss.pgh.pa.us> (raw)
In-Reply-To: <50881D73.100@optionshouse.com>
References: <50881D73.100@optionshouse.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sthomas@optionshouse.com
Subject: Re: Setting Statistics on Functional Indexes
In-Reply-To: <6668.1351105908@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox