Partial Indexes Not being Used [WAS]Re: Partial_indexes (Immutable?)

On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
> Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)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')