[Bizgres-general] Problem with EXPLAIN Plan

[Mark Kirkwood]

Milen Kulev wrote:
>
> Another Problem is the pretty high BITMAP index creation times. Details:
>
>  bizdb=# CREATE INDEX  idx_countries_id_bmp_ ON fact using bitmap (country_id)  ;
> CREATE INDEX
> Time: 67401.643 ms
> bizdb=# CREATE INDEX  idx_times_id_bmp_ ON fact using bitmap (time_id)  ;
> CREATE INDEX
> Time: 96333.344 ms
> bizdb=# CREATE INDEX  idx_products_id_bmp_ ON fact using bitmap (product_id)  ;
> CREATE INDEX
> Time: 814897.540 ms
> bizdb=# CREATE INDEX  idx_customers_id_bmp_ ON fact using bitmap (customer_id)  ;
> CREATE INDEX
> Time: 239735.302 ms
>
>   

(I was a bit quick with the 'send' key....)

It is probably interesting to find out the # of distinct values for the 
various indexed columns,
as in general a higher # of distinct values will make the bitmap index 
take longer to create.

At a glance (and checking the on disk sizes of the bitmap index 
relation), I would guess:

- country_id has a low # of distinct values
- product_id has a high # of distinct values

In terms of speeding this up, try increasing the maintenance_work_mem 
postgresql.conf parameter!

Cheers

Mark