[Paper Review] Exact Selectivity Computation for Modern In-Memory Database Query Optimization

Jun Hyung Shin, Florin Rusu+1 more|arXiv (Cornell University)|Jan 6, 2019

Data Management and Algorithms11 references4 citations

TL;DR

This paper proposes Exact Selectivity Computation (ESC), a novel query optimization paradigm for in-memory and GPU-accelerated databases that computes selectivities exactly via sub-queries during optimization, avoiding reliance on inaccurate synopses. ESC achieves sub-30ms overhead on GPU and improves query execution plans by up to 32X faster execution through exact cardinality estimation and materialization of filtered results.

ABSTRACT

Selectivity estimation remains a critical task in query optimization even after decades of research and industrial development. Optimizers rely on accurate selectivities when generating execution plans. They maintain a large range of statistical synopses for efficiently estimating selectivities. Nonetheless, small errors -- propagated exponentially -- can lead to severely sub-optimal plans---especially, for complex predicates. Database systems for modern computing architectures rely on extensive in-memory processing supported by massive multithread parallelism and vectorized instructions. However, they maintain the same synopses approach to query optimization as traditional disk-based databases. We introduce a novel query optimization paradigm for in-memory and GPU-accelerated databases based on extit{exact selectivity computation (ESC)}. The central idea in ESC is to compute selectivities exactly through queries during query optimization. In order to make the process efficient, we propose several optimizations targeting the selection and materialization of tables and predicates to which ESC is applied. We implement ESC in the MapD open-source database system. Experiments on the TPC-H and SSB benchmarks show that ESC records constant and less than 30 milliseconds overhead when running on GPU and generates improved query execution plans that are as much as 32X faster.

Motivation & Objective

• To address the persistent problem of inaccurate selectivity estimation in query optimization, especially for complex predicates and skewed data.
• To eliminate reliance on statistical synopses like histograms and samples, which often fail to capture attribute correlations.
• To enable accurate, exact selectivity computation in modern in-memory and GPU-accelerated databases where low-latency execution is feasible.
• To improve query execution plans by using exact cardinalities, leading to better join order selection and performance.
• To minimize the performance cost of exact computation through selective application and result materialization.

Proposed method

• ESC computes selectivity by executing a sub-query during optimization to determine the exact number of rows matching a complex predicate.
• The optimizer selects specific tables and predicates for ESC based on complexity and potential performance gain.
• The result of the sub-query is materialized and reused in the final execution plan, avoiding recomputation.
• The approach is integrated into the MapD open-source database with minimal changes to the query optimizer.
• Optimizations include selective application of ESC to high-impact predicates and leveraging GPU parallelism for low-latency sub-query execution.
• Materialization is preferred over pipelining in in-memory systems to improve cache efficiency and reduce I/O.

Experimental results

Research questions

• RQ1 Can exact selectivity computation via sub-queries outperform traditional synopsis-based estimation in complex, correlated, or skewed data scenarios?
• RQ2 What is the performance overhead of exact selectivity computation when executed on modern GPU-accelerated in-memory databases?
• RQ3 Does exact selectivity lead to better query execution plans, and by how much can performance improve?
• RQ4 How does the overhead of ESC scale with increasing predicate complexity and number of attributes?
• RQ5 Can materialization of sub-query results further reduce execution time despite the initial computation cost?

Key findings

• ESC achieves constant overhead of less than 30 milliseconds on GPU across all tested workloads, including complex predicates and high attribute counts.
• On the TPC-H benchmark, ESC improved query execution speed by up to 32.27X, especially when better join orders were selected.
• On the SSB benchmark with scale factor 80, ESC achieved up to 7X speedup, with the largest gains in more selective queries.
• On CPU, ESC overhead increased with more attributes due to cache effects, but remained under 340ms even for four attributes.
• GPU execution of ESC sub-queries was consistently faster than CPU, with a 4.88X performance advantage observed in some TPC-H queries.
• Materialization of filtered results significantly reduced execution time by eliminating repeated filtering in the final plan.