I wrote:
> I thought of a fairly miserable hack, which relies on the fact that 8.0
> does know how to accumulate statistics on functional indexes:
Never mind, it turns out that doesn't work the way I thought. It's
actually falling back to a default estimate :-(. I still think it'd
be a good idea to use stats on partial indexes in future releases,
but right at the moment we aren't doing any such thing.
Here's an even more miserable hack: use a non-partial functional index
over a multicolumn expression as a poor man's way of creating
cross-column stats. For example, assuming all this_group_id values are
positive:
group=# create function myfunc(int,int) returns int as
group-# 'SELECT CASE WHEN $2 IS NULL THEN $1 ELSE -$1 END' language sql
immutable;
group=# create index fooi2 on group_data (myfunc(this_group_id, group_id));
CREATE INDEX
group=# analyze group_data;
ANALYZE
group=# explain analyze select * from group_data where myfunc(this_group_id,
group_id)=46;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using fooi2 on group_data (cost=0.00..2948.85 rows=792 width=43)
(actual time=0.171..0.198 rows=4 loops=1)
Index Cond: (CASE WHEN (group_id IS NULL) THEN this_group_id ELSE (-
this_group_id) END = 46)
Total runtime: 0.304 ms
(3 rows)
Dunno if you're desperate enough to try that ... but it does seem to work.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
