Skip site navigation (1) Skip section navigation (2)

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

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Partial Indexes Not being Used [WAS]Re: Partial_indexes(Immutable?)
Date: 2008-08-26 04:40:25
Message-ID: 1219725625.9629.28.camel@neuromancer.home.net (view raw or flat)
Thread:
Lists: pgsql-general
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')



In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-08-26 04:58:51
Subject: Re: Partial Indexes Not being Used [WAS]Re: Partial_indexes (Immutable?)
Previous:From: Vishal AroraDate: 2008-08-26 04:18:02
Subject: Re: Regarding access to a user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group