Home / Current Issue / Paper 1725087
Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures
Subject area: Science,Engineering and Technology · Area of research: Architecture
Abstract
Choosing between a clustered B-tree and a clustered columnstore index is one of the most consequential physical design decisions in a relational database, yet it is often made on intuition rather than evidence. This study compares the two architectures in terms of query execution latency, buffer pool efficiency, and recovery behavior. Using an analytical cost model applied to a TPC-H decision-support workload (Transaction Processing Performance Council, 2017), it finds that columnstore organization cuts analytical query latency by about an order of magnitude and makes far better use of memory through compression and column elimination, while the B-tree remains clearly superior for point lookups, short range queries, and frequent single-row updates. The advantage shifts from one design to the other at low selectivity, roughly 0.1 to 1 percent of rows, and this boundary depends on data ordering and on how many columns a query uses. The two designs also differ in logging and recovery cost. The study concludes that physical design should follow measured workload characteristics, and that hybrid configurations offer the best balance for mixed transactional and analytical systems.
Keywords
B-tree, batch execution, buffer pool, Columnstore index, compression, crash recovery, hybrid transactional and analytical processing, query latency, write-ahead logging
How to cite this paper
@article{1725087,
author = {Evans Ighodalo, Aisha Ife Abdulwahab, Emmanuel Chukwuma Okeke},
title = {Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures},
journal = {Iconic Research And Engineering Journals},
year = {2018},
volume = {2},
number = {5},
pages = {579-595},
issn = {2456-8880},
url = {https://www.irejournals.com/formatedpaper/1725087.pdf},
abstract = {Choosing between a clustered B-tree and a clustered columnstore index is one of the most consequential physical design decisions in a relational database, yet it is often made on intuition rather than evidence. This study compares the two architectures in terms of query execution latency, buffer pool efficiency, and recovery behavior. Using an analytical cost model applied to a TPC-H decision-support workload (Transaction Processing Performance Council, 2017), it finds that columnstore organization cuts analytical query latency by about an order of magnitude and makes far better use of memory through compression and column elimination, while the B-tree remains clearly superior for point lookups, short range queries, and frequent single-row updates. The advantage shifts from one design to the other at low selectivity, roughly 0.1 to 1 percent of rows, and this boundary depends on data ordering and on how many columns a query uses. The two designs also differ in logging and recovery cost. The study concludes that physical design should follow measured workload characteristics, and that hybrid configurations offer the best balance for mixed transactional and analytical systems.},
keywords = {B-tree, batch execution, buffer pool, Columnstore index, compression, crash recovery, hybrid transactional and analytical processing, query latency, write-ahead logging},
month = {November},
}