Re: Issue with creation of Partial_indexes (Immutable?)

On Mon, 2008-08-25 at 12:23 +0800, Ow Mun Heng wrote:
> CREATE INDEX idx_d_trh_code_id_partial
>   ON xmms.d_trh_table
>   USING btree
>   (code_id) where code_id not in ('P000','000') and code_id is not null;
> ERROR:  functions in index predicate must be marked IMMUTABLE
>
> Just trying something new.
>
> I want to create partial indexes on code_id which are not null and not
> P000/000
>
> the ones I want are like HMD11 or UE935 or OIOR11 etc.
>
> not sure where the IMMUTABLE part is coming from..
>

BTW, this is on 8.2.9 Seems to work OK on 8.3.3.

However, got a question on usage of this index.

I've got 2 indexes.

case #1
> CREATE INDEX idx_d_trh_code_id
>   ON xmms.d_trh_table
>   USING btree
>   (code_id)

case#2

> CREATE INDEX idx_d_trh_code_id_partial
>   ON xmms.d_trh_table
>   USING btree
>   (code_id)where code_id not in ('P000','000') and code_id is not null;

when I do a

select * from d_trh_table where code_id = 'UAH11'

it will still use the full index which is idx_d_trh_code_id instead of the partial index.

it is only when I do a

select * from d_trh_table where code_id = 'UAH11' and code_id not in
('P000','000') will it use the partial index.

I would _think_ that this is expected based on the documentation I'm
reading. Is it?
(need confirmation)