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
Scanusing 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