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)

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:

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

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

I suppose code_id is varchar or some such?

Try "where code_id::text not in ...". There's an array type coercion
underlying the right-hand side of the NOT IN, and 8.2 had some problems
with correctly identifying the volatility of such coercions.

regards, tom lane

On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:

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

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

I suppose code_id is varchar or some such?

Yep

Try "where code_id::text not in ...". There's an array type coercion
underlying the right-hand side of the NOT IN, and 8.2 had some problems
with correctly identifying the volatility of such coercions.

This now works.

Prior to that, I was trying
WHERE code_id::text <> 'P000'::text OR code_id::text <> '000'::text
Which is basically a variant of the above (only that I didn't realise
it!)

After a few more investigation on the usefulness of the partial indexes,
I found that, it really isn't all that useful, perhaps some experts can
shed some light.

explain analyse select * from d_trh_pbert where code_id IN( 'HAMA2');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_d_trh_pbert_eval on d_trh_pbert (cost=0.00..26669.96 rows=7125 width=216) (actual time=0.066..2.491 rows=1840 loops=1)
Index Cond: ((code_id)::text = 'HAMA2'::text)
Total runtime: 4.018 ms

explain analyse select * from d_trh_pbert where code_id IN( 'HAMA2','HAMA3');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on d_trh_pbert (cost=262.02..53641.68 rows=14249 width=216) (actual time=0.926..4.858 rows=3556 loops=1)
Recheck Cond: ((code_id)::text = ANY (('{HAMA2,HAMA3}'::character varying[])::text[]))
-> Bitmap Index Scan on idx_d_trh_pbert_eval (cost=0.00..258.45 rows=14249 width=0) (actual time=0.853..0.853 rows=3556 loops=1)
Index Cond: ((code_id)::text = ANY (('{HAMA2,HAMA3}'::character varying[])::text[]))
Total runtime: 7.809 ms

It doesn't even hit the partial indexes.

explain analyse select * from d_trh_pbert where code_id IN( 'HAMA2','HAMA3') and code_id not in ('P000','000') and code_id is not null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on d_trh_pbert (cost=259.90..53675.18 rows=5788 width=216) (actual time=0.916..7.477 rows=3556 loops=1)
Recheck Cond: ((code_id)::text = ANY (('{HAMA2,HAMA3}'::character varying[])::text[]))
Filter: ((code_id IS NOT NULL) AND ((code_id)::text <> ALL (('{P000,000}'::character varying[])::text[])))
-> Bitmap Index Scan on idx_d_trh_pbert_eval (cost=0.00..258.45 rows=14249 width=0) (actual time=0.835..0.835 rows=3556 loops=1)
Index Cond: ((code_id)::text = ANY (('{HAMA2,HAMA3}'::character varying[])::text[]))
Total runtime: 10.510 ms

hmxmms=# explain analyse select * from d_trh_pbert where code_id IN( 'HAMA3') and code_id not in ('P000','000') and code_id is not null;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_d_trh_pbert_eval on d_trh_pbert (cost=0.00..26687.77 rows=2894 width=216) (actual time=0.077..3.506 rows=1716 loops=1)
Index Cond: ((code_id)::text = 'HAMA3'::text)
Filter: ((code_id)::text <> ALL (('{P000,000}'::character varying[])::text[]))
Total runtime: 5.025 ms

The 2 indexes.

CREATE INDEX idx_d_trh_pbert_eval_partial2
ON xmms.d_trh_pbert
USING btree
(code_id)
WHERE (code_id::text <> ALL (ARRAY['P000'::text, '000'::text])) AND code_id IS NOT NULL; (size ~500MB)

CREATE INDEX idx_d_trh_pbert_eval
ON xmms.d_trh_pbert
USING btree
(code_id); (size ~1.5G)

This table has approx 73 million rows and is 35 columns wide.
Stats on the code_id column is at 200 and there's ~1k of distinct values in it.

code_id is varchar(5)

I was hoping that doing the partial index will make things faster as ~70-80% of the time, it's ('P000','000')

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:

On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:

I suppose code_id is varchar or some such?

Yep
After a few more investigation on the usefulness of the partial indexes,
I found that, it really isn't all that useful, perhaps some experts can
shed some light.

I poked at that example a bit more earlier today, and found that 8.3
has a problem that's interfering with optimizing x IN ('y','z') type
clauses when x is varchar. If you don't mind building a local copy,
see if this patch helps you any:
http://archives.postgresql.org/pgsql-committers/2008-08/msg00254.php

regards, tom lane

On Tue, 2008-08-26 at 00:58 -0400, Tom Lane wrote:

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:

On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:

I suppose code_id is varchar or some such?

Yep
After a few more investigation on the usefulness of the partial indexes,
I found that, it really isn't all that useful, perhaps some experts can
shed some light.

I poked at that example a bit more earlier today, and found that 8.3
has a problem that's interfering with optimizing x IN ('y','z') type
clauses when x is varchar. If you don't mind building a local copy,
see if this patch helps you any:
http://archives.postgresql.org/pgsql-committers/2008-08/msg00254.php

I wouldn't mind if I have a box with a compiler installed or if i have a
NON-Production Box at all.

:-(