Re: Optimizing Bitmap Heap Scan.

On Tue, 8 Dec 2009, niraj patel wrote:
>  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)

> select count(*) from  cmrules;
> 
> Gives me 17 643 532 Rows

Looks good from here. Think about what you're asking the database to do. 
It has to select 272211 rows out of a large table with 17643532 rows. That 
in itself could take a very long time. It is clear that in your EXPLAIN 
this data is already cached, otherwise it would have to perform nigh on 
270000 seeks over the discs, which would take (depending on the disc 
system) something on the order of twenty minutes. Those 272211 rows then 
have to be sorted, which takes a couple of seconds, which again is pretty 
good. The rows are then uniqued, which is really quick, before returning 
the results.

It's hard to think how you would expect the database to do this any 
faster, really.

> 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)

You may perhaps benefit from an index on just the workspaceid column, but 
the benefit may be minor.

You may think of clustering the table on the index, but that will only be 
of benefit if the data is not in the cache.

The statistics seem to be pretty accurate, predicting 305821 instead of 
272211 rows. The database is not going to easily predict the number of 
unique results (9 instead of 261), but that doesn't affect the query plan 
much, so I wouldn't worry about it.

I would consider upgrading to Postgres 8.4 if possible, as it does have 
some considerable performance improvements, especially for bitmap index 
scans if you are using a RAID array. I'd also try using "SELECT DISTINCT" 
rather than "GROUP BY" and seeing if that helps.

Matthew

-- 
 Now the reason people powdered their faces back then was to change the values
 "s" and "n" in this equation here.                 - Computer science lecturer