Обсуждение: Forcing more agressive index scans for BITMAP AND

just wondering if there's a special tweak i can do to force more usage
of indexes to do BITMAP ands?

I have a table like

A int
B int
C int
D int
E int
F int
g int

where A/B/C/D/E are indexes

There's ~20millions rows in the table.

Query are something like this.

select * from table
where A=X
and B = Y
and C = Z
and D = AA
and E = BB

the query plan will only pick 2 indexes to do the bitmap.
I'm not sure how to tweak the config for it to use more indexes.

Box is a celeron 1.7 w/ 768MB ram with shared buffers at 250MB and
effective cache size 350MB

On Mon, 7 Apr 2008, Ow Mun Heng wrote:
> just wondering if there's a special tweak i can do to force more usage
> of indexes to do BITMAP ands?

There's no need to post this again. You have already had a couple of
useful answers.

Matthew

--
All of this sounds mildly turgid and messy and confusing... but what the
heck. That's what programming's all about, really
                                        -- Computer Science Lecturer

On Mon, 2008-04-07 at 11:50 +0100, Matthew wrote:
> On Mon, 7 Apr 2008, Ow Mun Heng wrote:
> > just wondering if there's a special tweak i can do to force more usage
> > of indexes to do BITMAP ands?
>
> There's no need to post this again. You have already had a couple of
> useful answers.

Sorry about this. I didn't see any responses(and my own mail) in my
INBOX (I'm subscribed to the list and should be receiving all the
messages) and thus I thought that it didn't go through. I didn't check
the internet arhives as I do not have internet access at the workplace.

I saw the answers from the list at home though and I'm trying to answer
those questions below.

To answer (based on what I see in pgadmin)

index A = 378 distinct values
index B = 235
index C = 53
index D = 32
index E = 1305
index F = 246993 (This is timestamp w/o timezone)

(note that this is just 1 table and there are no joins whatsoever.)

I moved from multicolumn indexes to individual indexes because the
queries does not always utilise the same few indexes, some users would
use

eg: index F, A, B or D,A,E or any other combination.

with regard to the fact that perhaps a sec scan is much IO efficient,
this is true when using index F (timestamp) of > 2 weeks interval, then
it will ignore the other indexes to be searched but do a filter.

"Bitmap Heap Scan on dtt  (cost=25109.93..30213.85 rows=1 width=264)"
"  Recheck Cond: (((A)::text = 'H3'::text) AND (F >= '2008-04-01 00:00:00'::timestamp without time zone) AND (F <=
'2008-04-0800:00:00'::timestamp without time zone))" 
"  Filter: (((B)::text = ANY (('{P000,000}'::character varying[])::text[])) AND ((C)::text ~~ 'F8.M.Y%'::text))"
"  ->  BitmapAnd  (cost=25109.93..25109.93 rows=1299 width=0)"
"        ->  Bitmap Index Scan on idx_dtt_A  (cost=0.00..986.12 rows=47069 width=0)"
"              Index Cond: ((A)::text = 'H3'::text)"
"        ->  Bitmap Index Scan on idx_dtt_date  (cost=0.00..24123.56 rows=1007422 width=0)"
"              Index Cond: ((F >= '2008-04-01 00:00:00'::timestamp without time zone) AND (F <= '2008-04-08
00:00:00'::timestampwithout time zone))" 


Changing the date to query from 3/10 to 4/8

"Bitmap Heap Scan on dtt  (cost=47624.67..59045.32 rows=1 width=264)"
"  Recheck Cond: (((A)::text = 'H3'::text) AND ((B)::text = 'MD'::text))"
"  Filter: ((F >= '2008-03-10 00:00:00'::timestamp without time zone) AND (F <= '2008-04-08 00:00:00'::timestamp
withouttime zone) AND ((B)::text = ANY (('{P000,000}'::character varying[])::text[])) AND ((C)::text ~~
'F8.M.Y%'::text))"
"  ->  BitmapAnd  (cost=47624.67..47624.67 rows=2944 width=0)"
"        ->  Bitmap Index Scan on idx_d_dtt  (cost=0.00..986.13 rows=47070 width=0)"
"              Index Cond: ((A)::text = 'H3'::text)"
"        ->  Bitmap Index Scan on idx_dtt_B  (cost=0.00..46638.29 rows=2283910 width=0)"
"              Index Cond: ((B)::text = 'MD'::text)"


I've seen many explains on my tables and IIRC never seen one in this it will use more than 2 indexes to do the query.

On Tue, 8 Apr 2008, Ow Mun Heng wrote:
> I moved from multicolumn indexes to individual indexes because the
> queries does not always utilise the same few indexes, some users would
> use
>
> eg: index F, A, B or D,A,E or any other combination.

Yes, that does make it more tricky, but it still may be best to use
multicolumn indexes. You would just need to create an index for each of
the combinations that you are likely to use.

Matthew

--
"To err is human; to really louse things up requires root
 privileges."                 -- Alexander Pope, slightly paraphrased