Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Sorry for the confusing and foolish question. However still I wonder
> why my expression(functional) index does not work.
You could likely have gotten it to match to a query likeSELECT ... WHERE (bid is not null) = true;
which would have the proper form of "(indexed value) = constant".
Whether the planner would have picked an indexscan for that without
coercion is another issue. IIRC 7.4 does not keep statistics for
functional indexes and so it is unlikely to get the rowcount estimates
right for a query expressed this way. (Looking back at your example,
you don't seem to have run an ANALYZE anyway :-()
A partial index is likely to work better for this problem on both
counts: you can write just "WHERE bid is not null", and the normal
stats will (I think) be able to estimate that well.
regards, tom lane