[PERFORM] Optimizing Bitmap Heap Scan.

[niraj patel]

Hi All,

I have to optimize following query :

SELECT r.TopFamilyID AS FamilyID,  FROM CMRules r 
           WHERE r.WorkspaceID =18512  
            GROUP BY r.TopFamilyID ;

The explain plan is as follows :

 Group  (cost=509989.19..511518.30 rows=9 width=10) (actual 
time=1783.102..2362.587 rows=261 loops=1)
   ->  Sort  (cost=509989.19..510753.74 rows=305821 width=10) (actual 
time=1783.097..2121.378 rows=272211 loops=1)
         Sort Key: topfamilyid
         ->  Bitmap Heap Scan on cmrules r  (cost=14501.36..476896.34 
rows=305821 width=10) (actual time=51.507..351.487 rows=272211 loops=1)
               Recheck Cond: (workspaceid = 18512::numeric)
               ->  Bitmap Index Scan on pk_ws_fea_fam_cmrules  
(cost=0.00..14424.90 rows=305821 width=0) (actual time=48.097..48.097 
rows=272211 loops=1)
                     Index Cond: (workspaceid = 18512::numeric)
 Total runtime: 2373.008 ms
(8 rows)
-----------------------------------------------------------------------------------------------------------------
\d CMRules gives follows indexes

Indexes:
    "pk_ws_fea_fam_cmrules" PRIMARY KEY, btree (workspaceid, featureid, 
topfamilyid, ruleenddate, gid)
    "idx_cmrules" btree (topfamilyid)
    "idx_gid_ws_cmrules" btree (gid, workspaceid)
-----------------------------------------------------------------------------------------------------------------
SELECT count(distinct r.TopFamilyID) FROM CMRules r  WHERE r.WorkspaceID =18512

Gives me 261 Rows 

SELECT count(r.TopFamilyID) FROM CMRules r  WHERE r.WorkspaceID =18512  ;

Gives me 272 211 Rows

select count(*) from  cmrules;

Gives me 17 643 532 Rows


Please suggest me something to optimize this query

Thanks 
Niraj Patel