From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Issue with creation of Partial_indexes (Immutable?) |
Date: | 2008-08-25 04:38:54 |
Message-ID: | 1219639134.2765.23.camel@neuromancer.home.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2008-08-25 06:35:07 | Re: on delete cascade slowing down delete |
Previous Message | Troy Rasiah | 2008-08-25 04:28:27 | Re: Connection to second database on server |