Thread: forcing use of more indexes (bitmap AND)
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.
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.
On Fri, Mar 14, 2008 at 12:28 AM, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote: > 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. > > 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 Would setting enable_bitmapscan=off do that? I'm not being sarcastic, I really don't know.
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'. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Fri, 2008-03-14 at 00:50 -0600, Scott Marlowe wrote: > On Fri, Mar 14, 2008 at 12:28 AM, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote: > > 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. > > > > 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 > > Would setting enable_bitmapscan=off do that? I'm not being sarcastic, > I really don't know. This is even worst as the planner would only use 1 index.
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))"
am Fri, dem 14.03.2008, um 15:06:56 +0800 mailte Ow Mun Heng folgendes: > > 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. > > > 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))" > > This plan doesn't match with the query above... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Fri, 2008-03-14 at 08:26 +0100, A. Kretschmer wrote: > am Fri, dem 14.03.2008, um 15:06:56 +0800 mailte Ow Mun Heng folgendes: > > > > 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. > > > > > > 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.27 rows=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))" > > > > > > This plan doesn't match with the query above... the query is based on a view Regardless of how it is, The question is, Is there a method to force it to use more indexes to satisfy a query. The most I've seen the planner use is 2 indexes even though the where clause uses up to 6 indexes.