Re: Forcing more agressive index scans for BITMAP AND - Mailing list pgsql-performance

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.