On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
Ow Mun Heng [EMAIL PROTECTED] 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')
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
