Thread: Issue with creation of Partial_indexes (Immutable?)
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..
Clue?
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.491rows=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=3556loops=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_idis 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=3556loops=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_idis 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.506rows=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.
:-(