International Peer-Reviewed Journal•Open Access•ISSN 2456-8880
[email protected]•+91-7433024337

Home / Current Issue / Paper 1725087

1725087 Vol 2 · Issue 5 Download Paper

Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures

Evans Ighodalo Aisha Ife Abdulwahab Emmanuel Chukwuma Okeke

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

Evans Ighodalo, Aisha Ife Abdulwahab, Emmanuel Chukwuma Okeke "Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures" Iconic Research And Engineering Journals Volume 2 Issue 5 2018 Page 579-595
Evans Ighodalo, Aisha Ife Abdulwahab, Emmanuel Chukwuma Okeke "Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures" Iconic Research And Engineering Journals, vol. 2, no. 5, Nov. 2018
Evans Ighodalo, Aisha Ife Abdulwahab, Emmanuel Chukwuma Okeke (2018). Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures. Iconic Research And Engineering Journals, 2(5).
Evans Ighodalo, Aisha Ife Abdulwahab, Emmanuel Chukwuma Okeke "Evaluating Query Execution Latency and Buffer Pool Efficiency: A Comparative Analysis of Clustered Columnstore Versus B-Tree Index Architectures" Iconic Research And Engineering Journals, vol. 2, no. 5, Nov. 2018.
@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},
  }