Re: forcing use of more indexes (bitmap AND) - Mailing list pgsql-general

On Fri, 2008-03-14 at 07:53 +0100, A. Kretschmer wrote:
> am  Fri, dem 14.03.2008, um 14:28:15 +0800 mailte Ow Mun Heng folgendes:
> > query is something like this
> >
> >     Select *
> >     from v_test
> >     where acode Like 'PC%'
> >     and rev = '0Q'
> >     and hcm = '1'
> >     and mcm = 'K'
> >
> > where acode, rev, hcm, mcm are all indexes.
> >
> > Currently this query is only using the rev and mcm for the bitmapAND.
> > it then does a bitmap heap scan using the acode and the hcm indexes.
>
>
> Please show us the output generated from 'explain analyse select ...'
> Please tell us your PG-Version.
>
>
> > I would like to try to see if forcing the planner to favour heavier usage of the indexes would yield faster
results.
> >
> > I've tried lowering random_page_cost(default 4) down to 2 with no change in planner.
>
> The planner will use the index only if he assume it make sense.
> For instance, it make no sense to use the index if almost all rows 'hcm'
> contains '1'


pg_version 8.2.5

"Nested Loop Left Join  (cost=6482.71..9605.00 rows=4 width=347)"
"  ->  Nested Loop Left Join  (cost=6482.71..9536.68 rows=4 width=305)"
"        ->  Hash Left Join  (cost=6482.71..9461.26 rows=4 width=297)"
"              Hash Cond: (((famid.product_family)::text = dcm.product_family) AND ((a.preamp_dcm)::text =
(dcm.preamp_dcm)::text))"
"              ->  Nested Loop Left Join  (cost=6474.69..9453.09 rows=4 width=242)"
"                    ->  Bitmap Heap Scan on d_trh_pbert a  (cost=6474.69..9419.97 rows=4 width=237)"
"                          Recheck Cond: (((mecm)::text = 'K'::text) AND ((rev)::text = '0Q'::text))"
"                          Filter: (((acode)::text ~~ 'PC%'::text) AND ((hcm)::text = '1'::text))"
"                          ->  BitmapAnd  (cost=6474.69..6474.69 rows=747 width=0)"
"                                ->  Bitmap Index Scan on idx_d_trh_pbert_mediadcm  (cost=0.00..3210.50 rows=164046
width=0)"
"                                      Index Cond: ((media_dcm)::text = 'MK-0'::text)"
"                                ->  Bitmap Index Scan on idx_d_trh_pbert_ast  (cost=0.00..3263.93 rows=148130
width=0)"
"                                      Index Cond: ((rev)::text = '0Q'::text)"
"                    ->  Index Scan using driv_family_identifier_lookup_pkey on driv_family_identifier_lookup famid
(cost=0.00..8.27rows=1 width=17)" 
"                          Index Cond: ((famid.family_identifier)::text = (a.family_identifier)::text)"
"              ->  Hash  (cost=5.61..5.61 rows=161 width=76)"
"                    ->  Seq Scan on lookup_preamp_dcm dcm  (cost=0.00..5.61 rows=161 width=76)"
"        ->  Index Scan using d_trr_iw_pkey on d_trr_iw b  (cost=0.00..18.83 rows=1 width=38)"
"              Index Cond: (((a.serial_number)::text = (b.serial_number)::text) AND (a.head_id = b.head_id) AND
(a.test_run_start_date_time= b.test_run_start_date_time) AND (a.test_type = b.test_type) AND (a.test_phase_id =
b.test_phase_id))"
"  ->  Index Scan using d_trr_dfh_pkey on d_trr_dfh c  (cost=0.00..16.87 rows=1 width=72)"
"        Index Cond: (((a.serial_number)::text = (c.serial_number)::text) AND (a.head_id = c.head_id) AND
(a.test_run_start_date_time= c.test_run_start_date_time) AND (a.test_type = c.test_type) AND (a.test_phase_id =
c.test_phase_id))"