[Bizgres-general] Problem with EXPLAIN Plan
Milen Kulev
makulev at gmx.net
Thu Jun 15 22:07:27 UTC 2006
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.
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) ;
Best Regards.
Milen
More information about the Bizgres-general
mailing list