Re: forcing use of more indexes (bitmap AND)

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