research-article

Estimating Cardinalities with Deep Sketches

Authors:

Dimitri Vorona,

Bernhard Radke,

Thomas Neumann,

Alfons KemperAuthors Info & Claims

SIGMOD '19: Proceedings of the 2019 International Conference on Management of Data

Pages 1937 - 1940

https://doi.org/10.1145/3299869.3320218

Published: 25 June 2019 Publication History

Abstract

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.

References

[1]

Learned Cardinalities in PyTorch. https://github.com/andreaskipf/learnedcardinalities.

[2]

PyTorch. https://pytorch.org/.

[3]

TensorBoard. https://www.tensorflow.org/tensorboard.

[4]

S. Hasan, S. Thirumuruganathan, J. Augustine, N. Koudas, and G. Das. Multi-Attribute Selectivity Estimation Using Deep Learning. CoRR, abs/1903.09999, 2019.

[5]

S. Jain, J. Yan, T. Cruanes, and B. Howe. Database-Agnostic Workload Management. In CIDR, 2019.

[6]

A. Kemper and T. Neumann. HyPer: A Hybrid OLTP & OLAP Main Memory Database System Based on Virtual Memory Snapshots. In ICDE, 2011.

Digital Library

[7]

A. Kipf, T. Kipf, B. Radke, V. Leis, P. A. Boncz, and A. Kemper. Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In CIDR, 2019.

[8]

T. Kraska, M. Alizadeh, A. Beutel, E. H. Chi, A. Kristo, G. Leclerc, S. Madden, H. Mao, and V. Nathan. SageDB: A Learned Database System. In CIDR, 2019.

[9]

T. Kraska, A. Beutel, E. H. Chi, J. Dean, and N. Polyzotis. The Case for Learned Index Structures. In SIGMOD, 2018.

Digital Library

[10]

S. Krishnan, Z. Yang, K. Goldberg, J. M. Hellerstein, and I. Stoica. Learning to Optimize Join Queries With Deep Reinforcement Learning. CoRR, abs/1808.03196, 2018.

[11]

V. Leis, A. Gubichev, A. Mirchev, P. Boncz, A. Kemper, and T. Neumann. How Good Are Query Optimizers, Really? PVLDB, 2015.

Digital Library

[12]

V. Leis, B. Radke, A. Gubichev, A. Mirchev, P. Boncz, A. Kemper, and T. Neumann. Query Optimization Through the Looking Glass, and What We Found Running the Join Order Benchmark. VLDBJ, 2018.

Digital Library

[13]

X. Liang, A. J. Elmore, and S. Krishnan. Opportunistic View Materialization with Deep Reinforcement Learning. CoRR, abs/1903.01363, 2019.

[14]

R. Marcus and O. Papaemmanouil. Deep Reinforcement Learning for Join Order Enumeration. In aiDM, 2018.

Digital Library

[15]

R. Marcus and O. Papaemmanouil. Plan-Structured Deep Neural Network Models for Query Performance Prediction. CoRR, abs/1902.00132, 2019.

[16]

R. Marcus and O. Papaemmanouil. Towards a Hands-Free Query Optimizer through Deep Learning. In CIDR, 2019.

[17]

G. Moerkotte, T. Neumann, and G. Steidl. Preventing Bad Plans by Bounding the Impact of Cardinality Estimation Errors. PVLDB, 2009.

Digital Library

[18]

J. Ortiz, M. Balazinska, J. Gehrke, and S. S. Keerthi. Learning State Representations for Query Optimization with Deep Reinforcement Learning. In DEEM, 2018.

Digital Library

[19]

I. Trummer, S. Moseley, D. Maram, S. Jo, and J. Antonakakis. SkinnerDB: Regret-Bounded Query Evaluation via Reinforcement Learning. PVLDB, 2018.

Digital Library

[20]

C. Wu, A. Jindal, S. Amizadeh, H. Patel, W. Le, S. Qiao, and S. Rao. Towards a Learning Optimizer for Shared Clouds. PVLDB, 2018.

Digital Library

[21]

Y. Wu, J. Yu, Y. Tian, R. Sidle, and R. Barber. Designing Succinct Secondary Indexing Mechanism by Exploiting Column Correlations. CoRR, abs/1903.11203, 2019.

[22]

M. Zaheer, S. Kottur, S. Ravanbakhsh, B. Pó czos, R. R. Salakhutdinov, and A. J. Smola. Deep Sets. In NIPS, 2017.

Digital Library

Cited By

Chen QHong YWu YYang TCui B(2024)CodingSketch: A Hierarchical Sketch with Efficient Encoding and Recursive Decoding2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00130(1592-1605)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00130
He ZGu TYu J(2024)Using query semantic and feature transfer fusion to enhance cardinality estimating of property graph queriesDisplays10.1016/j.displa.2024.10285485(102854)Online publication date: Dec-2024
https://doi.org/10.1016/j.displa.2024.102854
Qi KYu JHe Z(2023)A Cardinality Estimator in Complex Database Systems Based on TreeLSTMSensors10.3390/s2317736423:17(7364)Online publication date: 23-Aug-2023
https://doi.org/10.3390/s23177364
Show More Cited By

Index Terms

Estimating Cardinalities with Deep Sketches
1. Information systems
  1. Data management systems
    1. Database management system engines
      1. Database query processing
        Query optimization

Recommendations

Deep Learning Models for Selectivity Estimation of Multi-Attribute Queries
SIGMOD '20: Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data

Selectivity estimation - the problem of estimating the result size of queries - is a fundamental problem in databases. Accurate estimation of query selectivity involving multiple correlated attributes is especially challenging. Poor cardinality ...
Approximate Sketches
SIGMOD

Sketches are single-pass small-space data summaries that can quickly estimate the cardinality of join queries. However, sketches are not directly applicable to join queries with dynamic filter conditions --- where arbitrary selection predicate(s) are ...
BoundEst: Estimating Join Cardinalities with Tight Upper Bounds
Web and Big Data
Abstract
Cardinality estimation is a critical component of query optimization. Despite extensive research, achieving efficient and accurate estimation for join queries remains challenging. Estimating tight upper bounds for join cardinalities can help the ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '19: Proceedings of the 2019 International Conference on Management of Data

June 2019

2106 pages

ISBN:9781450356435

DOI:10.1145/3299869

General Chairs:
Peter Boncz
CWI & Vrije Universiteit Amsterdam, The Netherlands
,
Stefan Manegold
CWI & Universiteit Leiden, The Netherlands
,
Program Chairs:
Anastasia Ailamaki
EPFL, Switzerland
,
Amol Deshpande
University of Maryland, USA
,
Tim Kraska
MIT, USA

Copyright © 2019 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 25 June 2019

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS '19

Sponsor:

SIGMOD

SIGMOD/PODS '19: International Conference on Management of Data

June 30 - July 5, 2019

Amsterdam, Netherlands

Acceptance Rates

SIGMOD '19 Paper Acceptance Rate 88 of 430 submissions, 20%;

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

23
Total Citations
View Citations
571
Total Downloads

Downloads (Last 12 months)78
Downloads (Last 6 weeks)3

Reflects downloads up to 24 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Chen QHong YWu YYang TCui B(2024)CodingSketch: A Hierarchical Sketch with Efficient Encoding and Recursive Decoding2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00130(1592-1605)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00130
He ZGu TYu J(2024)Using query semantic and feature transfer fusion to enhance cardinality estimating of property graph queriesDisplays10.1016/j.displa.2024.10285485(102854)Online publication date: Dec-2024
https://doi.org/10.1016/j.displa.2024.102854
Qi KYu JHe Z(2023)A Cardinality Estimator in Complex Database Systems Based on TreeLSTMSensors10.3390/s2317736423:17(7364)Online publication date: 23-Aug-2023
https://doi.org/10.3390/s23177364
Woltmann LThiessat JHartmann CHabich DLehner W(2023)FASTgres: Making Learned Query Optimizer Hinting EffectiveProceedings of the VLDB Endowment10.14778/3611479.361152816:11(3310-3322)Online publication date: 24-Aug-2023
https://dl.acm.org/doi/10.14778/3611479.3611528
Negi PWu ZKipf ATatbul NMarcus RMadden SKraska TAlizadeh M(2023)Robust Query Driven Cardinality Estimation under Changing WorkloadsProceedings of the VLDB Endowment10.14778/3583140.358316416:6(1520-1533)Online publication date: 20-Apr-2023
https://dl.acm.org/doi/10.14778/3583140.3583164
Zhao JZhang HGao Y(2023)Efficient Query Re-optimization with Judicious Subquery SelectionsProceedings of the ACM on Management of Data10.1145/35893301:2(1-26)Online publication date: 20-Jun-2023
https://dl.acm.org/doi/10.1145/3589330
Luo ZCai SWang YOoi B(2023)Regularized Pairwise Relationship based Analytics for Structured DataProceedings of the ACM on Management of Data10.1145/35889361:1(1-27)Online publication date: 30-May-2023
https://dl.acm.org/doi/10.1145/3588936
Liu ZZhang YZhu YZhang RYang TXie KWang SLi TCui B(2023)TreeSensing: Linearly Compressing Sketches with FlexibilityProceedings of the ACM on Management of Data10.1145/35889101:1(1-28)Online publication date: 30-May-2023
https://dl.acm.org/doi/10.1145/3588910
Jahangiri SCarey MFreytag J(2022)Design trade-offs for a robust dynamic hybrid hash joinProceedings of the VLDB Endowment10.14778/3547305.354732715:10(2257-2269)Online publication date: 7-Sep-2022
https://dl.acm.org/doi/10.14778/3547305.3547327
Butrovich MLim WMa LRollinson JZhang WXia YPavlo AIves ZBonifati AEl Abbadi A(2022)Tastes Great! Less Filling! High Performance and Accurate Training Data Collection for Self-Driving Database Management SystemsProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3517845(617-630)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3517845
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents