[Bizgres-general] FW: Latest bitmap performance results..

[Ayush Parashar]

Hi All,

Posting the results for the latest bitmap patch (the patch is attached).

The index creation time has greatly reduced:
----------------------------------------------------------
                    After patch     Before      B-Tree
                    (latest)
                    (Bitmap)        (Bitmap)
----------------------------------------------------------
l_linenumber        171.146         374.5       412.1
l_quantity          249.018         547.2       937.8
l_shipmode          262.282         454.8       2217.1
o_orderstatus       70.5            83.54       241.3
o_orderpriority     90.6            108.52      679.1
c_mktsegment        7.3             10.9        51.3
c_nationkey         3.4             8.3         9.3
----------------------------------------------------------


Latest performance numbers:
INDEX SIZE in MBytes:
----------------------------------------------------------
                                Bitmap        Btree
----------------------------------------------------------
l_linenumber                    59            1285
l_quantity                      117           1804
l_shipmode                      58            1804
o_orderstatus                   5             321
o_orderpriority                 11            580
c_mktsegment                    1             45
c_nationkey                     2             32
----------------------------------------------------------

QUERY PERFORMANCE (time in seconds for query to finish):
(in case of bitmap all columns have bitmap index while in case of btree all
columns have btree index)
----------------------------------------------------------
           Bitmap  Btree
----------------------------------------------------------
Query 1    136.3   393.5
Query 2    60.7    391.6
Query 3    97.3    186.7
Query 4    102.5   282.9
Query 5    61.6    358.9
----------------------------------------------------------

Query 1:
SELECT sum(lineitem.l_discount)
FROM 
lineitem, orders, customer, nation
WHERE 
nation.n_name='UNITED STATES' AND
customer.c_mktsegment='AUTOMOBILE' AND
orders.o_orderstatus='P' AND
orders.o_orderpriority='2-HIGH' AND
lineitem.l_quantity=5 AND
lineitem.l_shipmode='AIR' AND
lineitem.l_linenumber=5 AND
customer.c_custkey=orders.o_custkey AND
orders.o_orderkey=lineitem.l_orderkey AND
nation.n_nationkey=customer.c_nationkey;

Query2:
SELECT avg(lineitem.l_tax)
FROM 
lineitem, orders
WHERE
orders.o_orderstatus='F' AND
orders.o_orderpriority='4-NOT SPECIFIED' AND
lineitem.l_linenumber=5 AND
lineitem.l_shipmode='TRUCK' AND
lineitem.l_quantity=2 AND
orders.o_orderkey=lineitem.l_orderkey;

Query 3, 4 and 5:
select count(*) from lineitem where l_linenumber=1;
select count(*) from lineitem where l_linenumber in (1,2) and l_shipmode in
('RAIL','TRUCK');
select count(*) from lineitem where l_linenumber=5 and l_shipmode='RAIL' and
l_quantity=18;

About performance results environment setup:
Machine: 64bit Dual CPU AMD Opteron, 2191.435 MHz, 2GBytes of RAM
running Redhat Enterprise Linux (2.6.10-1.741_FC3.rootsmp)
Tables used: lineitem, orders, customer
Data generated using dbgen for TPC-H scale factor of 10.


Cheers,
Ayush


-------------- next part --------------
A non-text attachment was scrubbed...
Name: bizgres-bitmapindex-03-22.patch.bz2
Type: application/octet-stream
Size: 10905 bytes
Desc: not available
Url : http://pgfoundry.org/pipermail/bizgres-general/attachments/20060323/b5f00e0a/attachment.obj