Re: Forcing more agressive index scans for BITMAP AND - Mailing list pgsql-performance
From | Ow Mun Heng |
---|---|
Subject | Re: Forcing more agressive index scans for BITMAP AND |
Date | |
Msg-id | 1207633371.26613.21.camel@neuromancer.home.net Whole thread Raw |
In response to | Re: Forcing more agressive index scans for BITMAP AND (Matthew <matthew@flymine.org>) |
Responses |
Re: Forcing more agressive index scans for BITMAP AND
|
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.
pgsql-performance by date: