Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

TKHist: Cardinality Estimation for Join Queries via Histograms with Dominant Attribute Correlation Finding

Renrui Li, Qingzhi Ma, Jiajie Xu, Lei Zhao, An Liu

TL;DR

The paper tackles inaccurate cardinality estimation for multi-table joins under the binary join framework by relaxing histogram uniformity and modeling bin-wise non-uniformity with TKHist, a top-$k$ histogram structure. It further demonstrates that correlations between join keys and filter predicates are pivotal and introduces the Dominant Join Path Correlation Discovery (DJPCD) to mitigate over-estimation. Empirical results on STATS-CEB and IMDB/JOB-light show TKHist achieves 2–3 orders of magnitude reduction in error variance compared with state-of-the-art methods, while maintaining comparable or smaller memory footprints and competitive end-to-end latency. The work provides practical benefits for query optimizers by enabling robust, scalable join cardinality estimation with updates and clear hyper-parameter trade-offs, and points to future enhancements such as bloom-filter–based optimizations and improved binning strategies.

Abstract

Cardinality estimation has long been crucial for cost-based database optimizers in identifying optimal query execution plans, attracting significant attention over the past decades. While recent advancements have significantly improved the accuracy of multi-table join query estimations, these methods introduce challenges such as higher space overhead, increased latency, and greater complexity, especially when integrated with the binary join framework. In this paper, we introduce a novel cardinality estimation method named TKHist, which addresses these challenges by relaxing the uniformity assumption in histograms. TKHist captures bin-wise non-uniformity information, enabling accurate cardinality estimation for join queries without filter predicates. Furthermore, we explore the attribute independent assumption, which can lead to significant over-estimation rather than under-estimation in multi-table join queries. To address this issue, we propose the dominating join path correlation discovery algorithm to highlight and manage correlations between join keys and filter predicates. Our extensive experiments on popular benchmarks demonstrate that TKHist reduces error variance by 2-3 orders of magnitude compared to SOTA methods, while maintaining comparable or lower memory usage.

TKHist: Cardinality Estimation for Join Queries via Histograms with Dominant Attribute Correlation Finding

TL;DR

The paper tackles inaccurate cardinality estimation for multi-table joins under the binary join framework by relaxing histogram uniformity and modeling bin-wise non-uniformity with TKHist, a top- histogram structure. It further demonstrates that correlations between join keys and filter predicates are pivotal and introduces the Dominant Join Path Correlation Discovery (DJPCD) to mitigate over-estimation. Empirical results on STATS-CEB and IMDB/JOB-light show TKHist achieves 2–3 orders of magnitude reduction in error variance compared with state-of-the-art methods, while maintaining comparable or smaller memory footprints and competitive end-to-end latency. The work provides practical benefits for query optimizers by enabling robust, scalable join cardinality estimation with updates and clear hyper-parameter trade-offs, and points to future enhancements such as bloom-filter–based optimizations and improved binning strategies.

Abstract

Cardinality estimation has long been crucial for cost-based database optimizers in identifying optimal query execution plans, attracting significant attention over the past decades. While recent advancements have significantly improved the accuracy of multi-table join query estimations, these methods introduce challenges such as higher space overhead, increased latency, and greater complexity, especially when integrated with the binary join framework. In this paper, we introduce a novel cardinality estimation method named TKHist, which addresses these challenges by relaxing the uniformity assumption in histograms. TKHist captures bin-wise non-uniformity information, enabling accurate cardinality estimation for join queries without filter predicates. Furthermore, we explore the attribute independent assumption, which can lead to significant over-estimation rather than under-estimation in multi-table join queries. To address this issue, we propose the dominating join path correlation discovery algorithm to highlight and manage correlations between join keys and filter predicates. Our extensive experiments on popular benchmarks demonstrate that TKHist reduces error variance by 2-3 orders of magnitude compared to SOTA methods, while maintaining comparable or lower memory usage.
Paper Structure (20 sections, 9 equations, 11 figures, 4 tables, 2 algorithms)

This paper contains 20 sections, 9 equations, 11 figures, 4 tables, 2 algorithms.

Table of Contents

  1. Introduction
  2. TKHist Framework for Join Queries
  3. Workflow Overview
  4. Dimension Reduction of Single Table
  5. TKHist Data Structure
  6. Join Query Processing
  7. Implementation and Optimization
  8. Join of TKHist
  9. Discovery of Correlation between Dominant Join Path and Filter Predicates
  10. Support for updates
  11. Error Guarantee
  12. Experiments
  13. Experimental Setup
  14. Overall performance
  15. Performance for Pure Join Queries
  16. ...and 5 more sections

Figures (11)

  • Figure 1: Proportion of top $k$ join paths in join results. The dataset is STATS, and the join query template is $user\bowtie badges\bowtie comments \bowtie votes \bowtie posts$.
  • Figure 2: TKHist workflow.
  • Figure 3: TKHist data structure vs histogram data structure.
  • Figure 4: Decomposition of multi-table join queries into sub-queries.
  • Figure 5: Distribution of end-to-end execution times for queries in STATS-CEB workload.
  • ...and 6 more figures