Re: Bug? 8.0 does not use partial index

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