How I Learned to Stop Worrying and Love Re-optimization
© 2019 IEEE. Cost-based query optimizers remain one of the most important components of database management systems for analytic workloads. Though modern optimizers select plans close to optimal performance in the common case, a small number of queries are an order of magnitude slower than they coul...
| Main Authors: | , , , |
|---|---|
| Format: | Article |
| Language: | English |
| Published: |
Institute of Electrical and Electronics Engineers (IEEE)
2021
|
| Online Access: | https://hdl.handle.net/1721.1/132279 |
| _version_ | 1826212046921793536 |
|---|---|
| author | Perron, Matthew Shang, Zeyuan Kraska, Tim Stonebraker, Michael |
| author_facet | Perron, Matthew Shang, Zeyuan Kraska, Tim Stonebraker, Michael |
| author_sort | Perron, Matthew |
| collection | MIT |
| description | © 2019 IEEE. Cost-based query optimizers remain one of the most important components of database management systems for analytic workloads. Though modern optimizers select plans close to optimal performance in the common case, a small number of queries are an order of magnitude slower than they could be. In this paper we investigate why this is still the case, despite decades of improvements to cost models, plan enumeration, and cardinality estimation. We demonstrate why we believe that a re-optimization mechanism is likely the most cost-effective way to improve end-to-end query performance. We find that even a simple re-optimization scheme can improve the latency of many poorly performing queries. We demonstrate that re-optimization improves the end-to-end latency of the top 20 longest running queries in the Join Order Benchmark by 27%, realizing most of the benefit of perfect cardinality estimation. |
| first_indexed | 2024-09-23T15:15:37Z |
| format | Article |
| id | mit-1721.1/132279 |
| institution | Massachusetts Institute of Technology |
| language | English |
| last_indexed | 2024-09-23T15:15:37Z |
| publishDate | 2021 |
| publisher | Institute of Electrical and Electronics Engineers (IEEE) |
| record_format | dspace |
| spelling | mit-1721.1/1322792021-09-21T03:12:58Z How I Learned to Stop Worrying and Love Re-optimization Perron, Matthew Shang, Zeyuan Kraska, Tim Stonebraker, Michael © 2019 IEEE. Cost-based query optimizers remain one of the most important components of database management systems for analytic workloads. Though modern optimizers select plans close to optimal performance in the common case, a small number of queries are an order of magnitude slower than they could be. In this paper we investigate why this is still the case, despite decades of improvements to cost models, plan enumeration, and cardinality estimation. We demonstrate why we believe that a re-optimization mechanism is likely the most cost-effective way to improve end-to-end query performance. We find that even a simple re-optimization scheme can improve the latency of many poorly performing queries. We demonstrate that re-optimization improves the end-to-end latency of the top 20 longest running queries in the Join Order Benchmark by 27%, realizing most of the benefit of perfect cardinality estimation. 2021-09-20T18:21:38Z 2021-09-20T18:21:38Z 2021-01-11T16:15:06Z Article http://purl.org/eprint/type/ConferencePaper https://hdl.handle.net/1721.1/132279 en 10.1109/ICDE.2019.00191 Proceedings - International Conference on Data Engineering Creative Commons Attribution-Noncommercial-Share Alike http://creativecommons.org/licenses/by-nc-sa/4.0/ application/pdf Institute of Electrical and Electronics Engineers (IEEE) arXiv |
| spellingShingle | Perron, Matthew Shang, Zeyuan Kraska, Tim Stonebraker, Michael How I Learned to Stop Worrying and Love Re-optimization |
| title | How I Learned to Stop Worrying and Love Re-optimization |
| title_full | How I Learned to Stop Worrying and Love Re-optimization |
| title_fullStr | How I Learned to Stop Worrying and Love Re-optimization |
| title_full_unstemmed | How I Learned to Stop Worrying and Love Re-optimization |
| title_short | How I Learned to Stop Worrying and Love Re-optimization |
| title_sort | how i learned to stop worrying and love re optimization |
| url | https://hdl.handle.net/1721.1/132279 |
| work_keys_str_mv | AT perronmatthew howilearnedtostopworryingandlovereoptimization AT shangzeyuan howilearnedtostopworryingandlovereoptimization AT kraskatim howilearnedtostopworryingandlovereoptimization AT stonebrakermichael howilearnedtostopworryingandlovereoptimization |