1,883 research outputs found
Forecasting the cost of processing multi-join queries via hashing for main-memory databases (Extended version)
Database management systems (DBMSs) carefully optimize complex multi-join
queries to avoid expensive disk I/O. As servers today feature tens or hundreds
of gigabytes of RAM, a significant fraction of many analytic databases becomes
memory-resident. Even after careful tuning for an in-memory environment, a
linear disk I/O model such as the one implemented in PostgreSQL may make query
response time predictions that are up to 2X slower than the optimal multi-join
query plan over memory-resident data. This paper introduces a memory I/O cost
model to identify good evaluation strategies for complex query plans with
multiple hash-based equi-joins over memory-resident data. The proposed cost
model is carefully validated for accuracy using three different systems,
including an Amazon EC2 instance, to control for hardware-specific differences.
Prior work in parallel query evaluation has advocated right-deep and bushy
trees for multi-join queries due to their greater parallelization and
pipelining potential. A surprising finding is that the conventional wisdom from
shared-nothing disk-based systems does not directly apply to the modern
shared-everything memory hierarchy. As corroborated by our model, the
performance gap between the optimal left-deep and right-deep query plan can
grow to about 10X as the number of joins in the query increases.Comment: 15 pages, 8 figures, extended version of the paper to appear in
SoCC'1
Learned Cardinalities: Estimating Correlated Joins with Deep Learning
We describe a new deep learning approach to cardinality estimation. MSCN is a
multi-set convolutional network, tailored to representing relational query
plans, that employs set semantics to capture query features and true
cardinalities. MSCN builds on sampling-based estimation, addressing its
weaknesses when no sampled tuples qualify a predicate, and in capturing
join-crossing correlations. Our evaluation of MSCN using a real-world dataset
shows that deep learning significantly enhances the quality of cardinality
estimation, which is the core problem in query optimization.Comment: CIDR 2019. https://github.com/andreaskipf/learnedcardinalitie
Estimating Cardinalities with Deep Sketches
We introduce Deep Sketches, which are compact models of databases that allow
us to estimate the result sizes of SQL queries. Deep Sketches are powered by a
new deep learning approach to cardinality estimation that can capture
correlations between columns, even across tables. Our demonstration allows
users to define such sketches on the TPC-H and IMDb datasets, monitor the
training process, and run ad-hoc queries against trained sketches. We also
estimate query cardinalities with HyPer and PostgreSQL to visualize the gains
over traditional cardinality estimators.Comment: To appear in SIGMOD'1
Sampling-Based Query Re-Optimization
Despite of decades of work, query optimizers still make mistakes on
"difficult" queries because of bad cardinality estimates, often due to the
interaction of multiple predicates and correlations in the data. In this paper,
we propose a low-cost post-processing step that can take a plan produced by the
optimizer, detect when it is likely to have made such a mistake, and take steps
to fix it. Specifically, our solution is a sampling-based iterative procedure
that requires almost no changes to the original query optimizer or query
evaluation mechanism of the system. We show that this indeed imposes low
overhead and catches cases where three widely used optimizers (PostgreSQL and
two commercial systems) make large errors.Comment: This is the extended version of a paper with the same title and
authors that appears in the Proceedings of the ACM SIGMOD International
Conference on Management of Data (SIGMOD 2016
FactorJoin: A New Cardinality Estimation Framework for Join Queries
Cardinality estimation is one of the most fundamental and challenging
problems in query optimization. Neither classical nor learning-based methods
yield satisfactory performance when estimating the cardinality of the join
queries. They either rely on simplified assumptions leading to ineffective
cardinality estimates or build large models to understand the data
distributions, leading to long planning times and a lack of generalizability
across queries.
In this paper, we propose a new framework FactorJoin for estimating join
queries. FactorJoin combines the idea behind the classical join-histogram
method to efficiently handle joins with the learning-based methods to
accurately capture attribute correlation. Specifically, FactorJoin scans every
table in a DB and builds single-table conditional distributions during an
offline preparation phase. When a join query comes, FactorJoin translates it
into a factor graph model over the learned distributions to effectively and
efficiently estimate its cardinality.
Unlike existing learning-based methods, FactorJoin does not need to
de-normalize joins upfront or require executed query workloads to train the
model. Since it only relies on single-table statistics, FactorJoin has small
space overhead and is extremely easy to train and maintain. In our evaluation,
FactorJoin can produce more effective estimates than the previous
state-of-the-art learning-based methods, with 40x less estimation latency, 100x
smaller model size, and 100x faster training speed at comparable or better
accuracy. In addition, FactorJoin can estimate 10,000 sub-plan queries within
one second to optimize the query plan, which is very close to the traditional
cardinality estimators in commercial DBMS.Comment: Paper accepted by SIGMOD 202
- …