On-Disk Bitmap Index In Bizgres

On-Disk Bitmap Index In Bizgres Ayush Parashar aparashar@greenplum.com and Jie Zhang jzhang@greenplum.com 1

Agenda • Introduction to On-Disk Bitmap Index • Bitmap index creation • Bitmap index creation performance - index size and creation time • Performance with varying cardinality • Query performance • Summary 2

Introduction • Access method specially efficient for low-cardinality column of high-dimensional fact table • Takes a fraction of space as compared to B-tree • Very less index creation time • Very effective on queries with multiple-conditions in where clause 3

Introduction: continued.. • Consider the following data: 4

Introduction: B-Tree representation 5

Introduction: Bitmap representation 6

Bitmap index creation • Steps in bitmap-index creation • Build bin indexes • Apply encoding schemes: Equality encoding scheme used in the present implementation • Apply compression schemes • Contributors: Jie Zhang, Mark Kirkwood, Gavin Sherry 7

Bitmap index creation: continued.. 8

On-disk Bitmap Index - Performance • Table definition from OSDL - DBT 3 benchmark • Size of table is corresponding to scale factor 10 for DBT 3 9

On-disk Bitmap Index - Index Size Performance • Index size - Size of bitmap index is a fraction of B-tree index 10

On-disk Bitmap Index - Creation Time Performance • Index creation time: Up to 7 times faster index creation 11

On-disk Bitmap Index - Performance with varying cardinality • Index size with varying cardinality: Total rows 250 million 12

On-disk Bitmap Index - Performance with varying cardinality • Index creation time with varying cardinality: Total rows 250 million 13

On-disk Bitmap Index - Query Performance 14

On-disk Bitmap Index - Query Performance • Query Performance: • Run1, Run2 and Run3 indicate that the same query has been run consecutively three-times 15

On-disk Bitmap Index - Query Performance • Example: Query I explain plan output • QUERY PLAN • ----------------------------------------------------------------------------- • Aggregate (cost=713682.71..713682.72 rows=1 width=9) • -> Merge Join (cost=706473.11..713678.58 rows=1649 width=9) • Merge Cond: ("outer".l_orderkey = "inner".o_orderkey) • -> Sort (cost=122798.17..122841.70 rows=17412 width=13) • Sort Key: lineitem.l_orderkey • -> Bitmap Heap Scan on lineitem (cost=57440.64..121571.69 rows=17412 width=13) • Recheck Cond: ((l_quantity = 2::numeric) AND (l_linenumber = 5) AND (l_shipmode = 'TRUCK'::bpchar)) • -> BitmapAnd (cost=57440.64..57440.64 rows=17412 width=0) • -> Bitmap Index Scan on l_quantity_bm_idx(on-disk bitmap index) (cost=0.00..4500.02 rows=1199721 width=0) Index Cond: (l_quantity = 2::numeric) • -> Bitmap Index Scan on l_linenumber_bm_idx(on-disk bitmap index) (cost=0.00..22779.89 rows=6278540 width=0) Index Cond: (l_linenumber = 5) • -> Bitmap Index Scan on l_shipmode_bm_idx(on-disk bitmap index) (cost=0.00..30160.23 rows=8318065 width=0) Index Cond: (l_shipmode = 'TRUCK'::bpchar) • -> Sort (cost=583674.94..587225.94 rows=1420397 width=4) • Sort Key: orders.o_orderkey • -> Bitmap Heap Scan on orders (cost=10318.21..372165.55 rows=1420397 width=4) • Recheck Cond: (o_orderpriority = '4-NOT SPECIFIED'::bpchar) • Filter: (o_orderstatus = 'F'::bpchar) • -> Bitmap Index Scan on o_orderpriority_bm_idx(on-disk bitmap index) (cost=0.00..10318.21 rows=2869489 width=0) Index Cond: (o_orderpriority = '4-NOT SPECIFIED'::bpchar) • (21 rows) 16

Summary • On-disk Bitmap Index: New feature in Bizgres 0.9 • Provides dramatic improvements in index creation time and space used by the index • Dramatically improves response time for large classes of ad hoc data-warehousing queries 17

Thanks Questions..? References • Bitmap Index Design and Evaluation: Chee-Yong Chan and Yannis E. Ioannidis • Compressed bitmap indices for efficient query processing: Kesheng Wu Ekow J. Otoo Arie Shoshani • On-Disk Bitmap Index Performance in Bizgres 0.9 - A Greenplum Whitepaper: http://bgn.greenplum.com • http://www.bizgres.org/home.php 18

On-Disk Bitmap Index In Bizgres