[Bizgres-general] Problem with EXPLAIN Plan
Mark Kirkwood
mkirkwood at greenplum.com
Thu Jun 15 22:44:48 UTC 2006
Milen Kulev wrote:
> Hi,
> I have just set up an DHW schema to tst Bizgres.
> I am executing the following SQL statement:
>
> explain analyze
> select p.product_name, sum (cell1), count(cell2)
> from
> fact f,
> products p,
> customers c,
> countries ct,
> times t
> where
> f.country_id = ct.country_id
> and f.customer_id= c.customer_id
> and f.product_id= p.product_id
> and f.time_id = t.time_id
> and c.customer_name='Customer1'
> and ct.country_name = 'Country2'
> and t.day_name between 'Day200' and 'Day400'
> and (p.product_name ='Product10' or p.product_name ='Product11')
> group by p.product_name
> order by p.product_name asc;
>
> And I habe got following executon plan:
>
> GroupAggregate (cost=2367.90..2367.93 rows=1 width=22) (actual time=4.630..4.630 rows=0 loops=1)
> -> Sort (cost=2367.90..2367.90 rows=1 width=22) (actual time=4.625..4.625 rows=0 loops=1)
> Sort Key: p.product_name
> -> Nested Loop (cost=9.10..2367.89 rows=1 width=22) (actual time=4.596..4.596 rows=0 loops=1)
> Join Filter: ("outer".country_id = "inner".country_id)
> -> Nested Loop (cost=9.10..2366.63 rows=1 width=26) (actual time=4.593..4.593 rows=0 loops=1)
> -> Nested Loop (cost=9.10..2359.07 rows=1 width=30) (actual time=4.590..4.590 rows=0 loops=1)
> Join Filter: ("inner".customer_id = "outer".customer_id)
> -> Index Scan using idx_customers_dim on customers c (cost=0.00..5.95 rows=1 width=4)
> (actual time=0.221..0.229 rows=1 loops=1)
> Index Cond: ((customer_name)::text = 'Customer1'::text)
> -> Nested Loop (cost=9.10..2345.62 rows=600 width=34) (actual time=1.508..3.962 rows=277
> loops=1)
> -> Bitmap Heap Scan on products p (cost=4.01..11.98 rows=2 width=18) (actual
> time=0.070..0.075 rows=2 loops=1)
> Recheck Cond: (((product_name)::text = 'Product10'::text) OR
> ((product_name)::text = 'Product11'::text))
> -> BitmapOr (cost=4.01..4.01 rows=2 width=0) (actual time=0.063..0.063 rows=0
> loops=1)
> -> Bitmap Index Scan on idx_products_dim (cost=0.00..2.00 rows=1 width=0)
> (actual time=0.035..0.035 rows=1 loops=1)
> Index Cond: ((product_name)::text = 'Product10'::text)
> -> Bitmap Index Scan on idx_products_dim (cost=0.00..2.00 rows=1 width=0)
> (actual time=0.023..0.023 rows=1 loops=1)
> Index Cond: ((product_name)::text = 'Product11'::text)
> -> Bitmap Heap Scan on fact f (cost=5.10..1162.91 rows=313 width=24) (actual
> time=0.717..1.508 rows=138 loops=2)
> Recheck Cond: (f.product_id = "outer".product_id)
> -> Bitmap Index Scan on idx_products_id_bmp_(on-disk bitmap index)
> (cost=0.00..5.10 rows=313 width=0) (actual time=0.382..0.385 rows=0 loops=2)
> Index Cond: (f.product_id = "outer".product_id)
> -> Index Scan using idx_times_dim on times t (cost=0.00..7.54 rows=1 width=4) (never executed)
> Index Cond: (((t.day_name)::text >= 'Day200'::text) AND ((t.day_name)::text <=
> 'Day400'::text) AND ("outer".time_id = t.time_id))
> -> Seq Scan on countries ct (cost=0.00..1.25 rows=1 width=4) (never executed)
> Filter: ((country_name)::text = 'Country2'::text)
> Total runtime: 5.161 ms
> (27 rows)
>
> Time: 89.490 ms
>
>
>
> Perofmance is excellent, but what means "(never executed)" on countries ct table (second to last line of the execution
> plan). I am sure that countries table is accessed.
>
>
One of the nodes above produces 0 rows, so we never get there (see
index scan of idx_products_id_bmp_
> 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
>
> Table sizes:
>
> relname | relkind | rows | size_in_kb
> ------------------------------------------+---------+-------+------------
> countries | TABLE | 20 | 8.00
> bm_internal_t2200idx_countries_id_bmp__0 | TABLE | 0 | 0.00
> fact | TABLE | 3e+06 | 260872.00
> bm_internal_t2200idx_times_id_bmp__0 | TABLE | 0 | 0.00
> bm_internal_t2200idx_products_id_bmp__0 | TABLE | 0 | 0.00
> bm_internal_t2200idx_customers_id_bmp__0 | TABLE | 0 | 0.00
> times | TABLE | 1000 | 112.00
> products | TABLE | 10000 | 1112.00
> customers | TABLE | 4000 | 448.00
> bm_internal_i2200idx_countries_id_bmp__0 | INDEX | 0 | 8.00
> idx_countries_id_bmp_ | INDEX | 3e+06 | 4872.00
> bm_internal_i2200idx_times_id_bmp__0 | INDEX | 0 | 8.00
> idx_times_id_bmp_ | INDEX | 3e+06 | 16064.00
> bm_internal_i2200idx_products_id_bmp__0 | INDEX | 0 | 8.00
> idx_products_id_bmp_ | INDEX | 3e+06 | 160312.00
> bm_internal_i2200idx_customers_id_bmp__0 | INDEX | 0 | 8.00
> idx_customers_id_bmp_ | INDEX | 3e+06 | 64144.00
> idx_times_dim | INDEX | 1000 | 48.00
> idx_products_dim | INDEX | 10000 | 368.00
> idx_customers_dim | INDEX | 4000 | 160.00
>
>
> The biggest table is FACT table (as it could be expected), around 260MB, but I believe
> That index creation time is pretty high though.
>
> What is "bm_internal_i2200idx_customers_id_bmp__0" ? Actually I have issued
>
> CREATE INDEX idx_customers_id_bmp_ ON fact using bitmap (customer_id) ;
>
The bitmap implementation uses a table to store the distinct values that
are in the bitmap. So there are two elements:
- the bitmap index relation itself
- a heap relation (distinct values plus offsets into the bitmap rel)
In fact if you dig around you will see that there is a btree index on
the heap relation - so there is actually 3 relations created when you
use bitmap indexes!
Cheers
Mark
More information about the Bizgres-general
mailing list