{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,3,31]],"date-time":"2026-03-31T19:00:42Z","timestamp":1774983642861,"version":"3.50.1"},"reference-count":79,"publisher":"Association for Computing Machinery (ACM)","issue":"1","license":[{"start":{"date-parts":[[2025,2,10]],"date-time":"2025-02-10T00:00:00Z","timestamp":1739145600000},"content-version":"vor","delay-in-days":0,"URL":"https:\/\/www.acm.org\/publications\/policies\/copyright_policy#Background"}],"funder":[{"DOI":"10.13039\/501100006374","name":"Deutsche Forschungsgemeinschaft","doi-asserted-by":"publisher","award":["LE-1416\/28-1"],"award-info":[{"award-number":["LE-1416\/28-1"]}],"id":[{"id":"10.13039\/501100006374","id-type":"DOI","asserted-by":"publisher"}]}],"content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Proc. ACM Manag. Data"],"published-print":{"date-parts":[[2025,2,10]]},"abstract":"<jats:p>Despite an ever-growing corpus of novel query optimization strategies, the interaction of the core components of query optimizers is still not well understood. This situation can be problematic for two main reasons: On the one hand, this may cause surprising results when two components influence each other in an unexpected way. On the other hand, this can lead to wasted effort in regard to both engineering and research, e.g., when an improvement for one component is dwarfed or entirely canceled out by problems of another component. Therefore, we argue that making improvements to a single optimization component requires a thorough understanding of how these changes might affect the other components. To achieve this understanding, we present results of a comprehensive experimental analysis of the interplay in the traditional optimizer architecture using the widely-used PostgreSQL system as prime representative. Our evaluation and analysis revisit the core building blocks of such an optimizer, i.e. per-column statistics, cardinality estimation, cost model, and plan generation. In particular, we analyze how these building blocks influence each other and how they react when faced with faulty input, such as imprecise cardinality estimates. Based on our results, we draw novel conclusions and make recommendations on how these should be taken into account.<\/jats:p>","DOI":"10.1145\/3709659","type":"journal-article","created":{"date-parts":[[2025,2,11]],"date-time":"2025-02-11T15:45:06Z","timestamp":1739288706000},"page":"1-28","source":"Crossref","is-referenced-by-count":2,"title":["An Elephant Under the Microscope: Analyzing the Interaction of Optimizer Components in PostgreSQL"],"prefix":"10.1145","volume":"3","author":[{"ORCID":"https:\/\/orcid.org\/0009-0005-1514-7253","authenticated-orcid":false,"given":"Rico","family":"Bergmann","sequence":"first","affiliation":[{"name":"Technische Universit\u00e4t Dresden, Dresden, Germany"}]},{"ORCID":"https:\/\/orcid.org\/0000-0002-5334-059X","authenticated-orcid":false,"given":"Claudio","family":"Hartmann","sequence":"additional","affiliation":[{"name":"Technische Universit\u00e4t Dresden, Dresden, Germany"}]},{"ORCID":"https:\/\/orcid.org\/0000-0002-8671-5466","authenticated-orcid":false,"given":"Dirk","family":"Habich","sequence":"additional","affiliation":[{"name":"Technische Universit\u00e4t Dresden, Dresden, Germany"}]},{"ORCID":"https:\/\/orcid.org\/0000-0001-8107-2775","authenticated-orcid":false,"given":"Wolfgang","family":"Lehner","sequence":"additional","affiliation":[{"name":"Technische Universit\u00e4t Dresden, Dresden, Germany"}]}],"member":"320","published-online":{"date-parts":[[2025,2,11]]},"reference":[{"key":"e_1_2_2_1_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611540.3611544"},{"key":"e_1_2_2_2_1","doi-asserted-by":"publisher","DOI":"10.18420\/BTW2023--14"},{"key":"e_1_2_2_3_1","doi-asserted-by":"publisher","DOI":"10.1145\/3299869.3319894"},{"key":"e_1_2_2_4_1","doi-asserted-by":"publisher","DOI":"10.14778\/3587136.3587150"},{"key":"e_1_2_2_5_1","doi-asserted-by":"publisher","DOI":"10.14778\/3598581"},{"key":"e_1_2_2_6_1","doi-asserted-by":"publisher","DOI":"10.14778\/3594512.3594525"},{"key":"e_1_2_2_7_1","doi-asserted-by":"publisher","DOI":"10.1007\/3--540--58907--4_6"},{"key":"e_1_2_2_8_1","doi-asserted-by":"publisher","DOI":"10.1145\/3588907"},{"key":"e_1_2_2_9_1","doi-asserted-by":"publisher","DOI":"10.1145\/3588963"},{"key":"e_1_2_2_10_1","volume-title":"DuckDB Query Optimizer","author":"DB","unstructured":"DuckDB Foundation 2024. DuckDB Query Optimizer. DuckDB Foundation. https:\/\/duckdb.org\/why_duckdb#standingon-the-shoulders-of-giants"},{"key":"e_1_2_2_11_1","doi-asserted-by":"publisher","DOI":"10.14778\/3329772.3329780"},{"key":"e_1_2_2_12_1","doi-asserted-by":"publisher","DOI":"10.1007\/3--540--58907--4_22"},{"key":"e_1_2_2_13_1","unstructured":"Edward Gilmore Stefan Hinz David Hollis Philip Olson Daniel So and Jon Stephens. 2024. MySQL Histogram Construction. MySQL Documentation Team. https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/analyze-table.html#analyzetable-histogram-statistics-analysis"},{"key":"e_1_2_2_14_1","unstructured":"Edward Gilmore Stefan Hinz David Hollis Philip Olson Daniel So and Jon Stephens. 2024. Query Plan Evaluation in MySQL. MySQL Documentation Team. https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/controlling-query-plan-evaluation. html"},{"key":"e_1_2_2_15_1","volume-title":"PostgreSQL Planner Method Parameters","author":"Haas Robert","unstructured":"Robert Haas. 2024. PostgreSQL Planner Method Parameters. PostgreSQL Global Development Group. https:\/\/www. postgresql.org\/docs\/16\/pgprewarm.html"},{"key":"e_1_2_2_16_1","doi-asserted-by":"publisher","DOI":"10.1145\/3588927"},{"key":"e_1_2_2_17_1","doi-asserted-by":"publisher","DOI":"10.1007\/978--3-030--18579--4_1"},{"key":"e_1_2_2_18_1","doi-asserted-by":"publisher","DOI":"10.14778\/3503585.3503586"},{"key":"e_1_2_2_19_1","doi-asserted-by":"publisher","DOI":"10.14778\/1920841.1921027"},{"key":"e_1_2_2_20_1","doi-asserted-by":"publisher","DOI":"10.1145\/3318464.3389741"},{"key":"e_1_2_2_21_1","volume-title":"11th Conference on Innovative Data Systems Research, CIDR","author":"Hertzschuch Axel","year":"2021","unstructured":"Axel Hertzschuch, Claudio Hartmann, Dirk Habich, and Wolfgang Lehner. 2021. Simplicity Done Right for Join Ordering. In 11th Conference on Innovative Data Systems Research, CIDR 2021, Virtual Event, January 11--15, 2021, Online Proceedings. www.cidrdb.org. http:\/\/cidrdb.org\/cidr2021\/papers\/cidr2021_paper01.pdf"},{"key":"e_1_2_2_22_1","doi-asserted-by":"publisher","DOI":"10.14778\/3551793.3551825"},{"key":"e_1_2_2_23_1","doi-asserted-by":"publisher","DOI":"10.14778\/3551793.3551799"},{"key":"e_1_2_2_24_1","doi-asserted-by":"publisher","DOI":"10.14778\/3384345.3384349"},{"key":"e_1_2_2_25_1","doi-asserted-by":"publisher","DOI":"10.1145\/3448016.3452840"},{"key":"e_1_2_2_26_1","doi-asserted-by":"publisher","DOI":"10.1145\/3514221"},{"key":"e_1_2_2_27_1","volume-title":"Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In 9th Biennial Conference on Innovative Data Systems Research, CIDR","author":"Kipf Andreas","year":"2019","unstructured":"Andreas Kipf, Thomas Kipf, Bernhard Radke, Viktor Leis, Peter A. Boncz, and Alfons Kemper. 2019. Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In 9th Biennial Conference on Innovative Data Systems Research, CIDR 2019, Asilomar, CA, USA, January 13--16, 2019, Online Proceedings. www.cidrdb.org. http: \/\/cidrdb.org\/cidr2019\/papers\/p101-kipf-cidr19.pdf"},{"key":"e_1_2_2_28_1","doi-asserted-by":"publisher","DOI":"10.1007\/S41019-020-00149--7"},{"key":"e_1_2_2_29_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611479.3611494"},{"key":"e_1_2_2_30_1","doi-asserted-by":"publisher","DOI":"10.14778\/2850583.2850594"},{"key":"e_1_2_2_31_1","volume-title":"8th Biennial Conference on Innovative Data Systems Research, CIDR","author":"Leis Viktor","year":"2017","unstructured":"Viktor Leis, Bernhard Radke, Andrey Gubichev, Alfons Kemper, and Thomas Neumann. 2017. Cardinality Estimation Done Right: Index-Based Join Sampling. In 8th Biennial Conference on Innovative Data Systems Research, CIDR 2017, Chaminade, CA, USA, January 8--11, 2017, Online Proceedings. www.cidrdb.org. http:\/\/cidrdb.org\/cidr2017\/papers\/p9-leis-cidr17.pdf"},{"key":"e_1_2_2_32_1","doi-asserted-by":"publisher","DOI":"10.1007\/S00778-017-0480--7"},{"key":"e_1_2_2_33_1","doi-asserted-by":"publisher","DOI":"10.14778\/3476249.3476254"},{"key":"e_1_2_2_34_1","doi-asserted-by":"publisher","DOI":"10.1145\/3514221.3517871"},{"key":"e_1_2_2_35_1","doi-asserted-by":"publisher","DOI":"10.1145\/3542700.3542703"},{"key":"e_1_2_2_36_1","doi-asserted-by":"publisher","DOI":"10.14778\/3342263.3342644"},{"key":"e_1_2_2_37_1","doi-asserted-by":"publisher","DOI":"10.1145\/3626755"},{"key":"e_1_2_2_38_1","doi-asserted-by":"publisher","DOI":"10.1145\/3626734"},{"key":"e_1_2_2_39_1","doi-asserted-by":"publisher","DOI":"10.1145\/1376616.1376672"},{"key":"e_1_2_2_40_1","doi-asserted-by":"publisher","DOI":"10.14778\/1687627.1687738"},{"key":"e_1_2_2_41_1","doi-asserted-by":"publisher","DOI":"10.14778\/3476249.3476259"},{"key":"e_1_2_2_42_1","doi-asserted-by":"publisher","DOI":"10.14778\/3583140.3583164"},{"key":"e_1_2_2_43_1","doi-asserted-by":"publisher","DOI":"10.1145\/3318464.3389727"},{"key":"e_1_2_2_44_1","volume-title":"pg_hint_plan","author":"Nippon Telegraph and Telephone Corporation 2024.","unstructured":"Nippon Telegraph and Telephone Corporation 2024. pg_hint_plan. Nippon Telegraph and Telephone Corporation. https:\/\/github.com\/ossc-db\/pg_hint_plan"},{"key":"e_1_2_2_45_1","volume-title":"PostgreSQL ANALYZE Command","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL ANALYZE Command. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/sql-analyze.html"},{"key":"e_1_2_2_46_1","volume-title":"PostgreSQL GEQO Configuration","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL GEQO Configuration. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO"},{"key":"e_1_2_2_47_1","volume-title":"PostgreSQL GEQO Optimizer","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL GEQO Optimizer. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/geqo.html"},{"key":"e_1_2_2_48_1","volume-title":"PostgreSQL Planner Method Parameters","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL Planner Method Parameters. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE"},{"key":"e_1_2_2_49_1","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL Planner\/Optimizer. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/planner-optimizer.html"},{"key":"e_1_2_2_50_1","volume-title":"PostgreSQL Statistics Generation","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL Statistics Generation. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/sql-analyze.html#id-1.9.3.46.8"},{"key":"e_1_2_2_51_1","volume-title":"PostgreSQL Statistics Target","author":"PostgreSQL Global Development Group 2024.","unstructured":"PostgreSQL Global Development Group 2024. PostgreSQL Statistics Target. PostgreSQL Global Development Group. https:\/\/www.postgresql.org\/docs\/16\/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET"},{"key":"e_1_2_2_52_1","volume-title":"Proceedings of the 31st International Conference on Very Large Data Bases","author":"Reddy Naveen","year":"2005","unstructured":"Naveen Reddy and Jayant R. Haritsa. 2005. Analyzing Plan Diagrams of Database Query Optimizers. In Proceedings of the 31st International Conference on Very Large Data Bases, Trondheim, Norway, August 30 - September 2, 2005, Klemens B\u00f6hm, Christian S. Jensen, Laura M. Haas, Martin L. Kersten, Per-\u00c5ke Larson, and Beng Chin Ooi (Eds.). ACM, 1228--1240. http:\/\/www.vldb.org\/archives\/website\/2005\/program\/paper\/fri\/p1228-reddy.pdf"},{"key":"e_1_2_2_53_1","doi-asserted-by":"publisher","DOI":"10.1145\/582095.582099"},{"key":"e_1_2_2_54_1","doi-asserted-by":"publisher","DOI":"10.14778\/3436905.3436907"},{"key":"e_1_2_2_55_1","doi-asserted-by":"publisher","DOI":"10.1007\/S00778-021-00689-Y"},{"key":"e_1_2_2_56_1","unstructured":"SQLite Contributors. 2024. SQLite Next-Generation Query Planner. https:\/\/www.sqlite.org\/queryplanner-ng.html"},{"key":"e_1_2_2_57_1","unstructured":"SQLite Contributors. 2024. SQLite Statistics Generation. https:\/\/sqlite.org\/lang_analyze.html"},{"key":"e_1_2_2_58_1","doi-asserted-by":"publisher","DOI":"10.14778\/3485450.3485459"},{"key":"e_1_2_2_59_1","doi-asserted-by":"publisher","DOI":"10.1145\/3639321"},{"key":"e_1_2_2_60_1","doi-asserted-by":"publisher","DOI":"10.14778\/3494124.3494126"},{"key":"e_1_2_2_61_1","unstructured":"Oleksii Vasyliev. 2023. PGTune Configuration Tool. https:\/\/pgtune.leopard.in.ua"},{"key":"e_1_2_2_62_1","doi-asserted-by":"publisher","DOI":"10.1145\/3147.3165"},{"key":"e_1_2_2_63_1","doi-asserted-by":"publisher","DOI":"10.1145\/3588708"},{"key":"e_1_2_2_64_1","doi-asserted-by":"publisher","DOI":"10.1007\/S00778-023-00808-X"},{"key":"e_1_2_2_65_1","doi-asserted-by":"publisher","DOI":"10.14778\/3461535.3461552"},{"key":"e_1_2_2_66_1","doi-asserted-by":"publisher","DOI":"10.14778\/3523210.3523221"},{"key":"e_1_2_2_67_1","doi-asserted-by":"publisher","DOI":"10.1145\/3329859.3329875"},{"key":"e_1_2_2_68_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611479.3611528"},{"key":"e_1_2_2_69_1","doi-asserted-by":"publisher","DOI":"10.1145\/3448016.3452830"},{"key":"e_1_2_2_70_1","doi-asserted-by":"publisher","DOI":"10.1145\/3588721"},{"key":"e_1_2_2_71_1","doi-asserted-by":"publisher","DOI":"10.1145\/3626769"},{"key":"e_1_2_2_72_1","doi-asserted-by":"publisher","DOI":"10.1145\/3514221.3517885"},{"key":"e_1_2_2_73_1","doi-asserted-by":"publisher","DOI":"10.14778\/3421424.3421432"},{"key":"e_1_2_2_74_1","doi-asserted-by":"publisher","DOI":"10.14778\/3368289.3368294"},{"key":"e_1_2_2_75_1","doi-asserted-by":"publisher","DOI":"10.14778\/3565838.3565846"},{"key":"e_1_2_2_76_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611479.3611501"},{"key":"e_1_2_2_77_1","doi-asserted-by":"publisher","DOI":"10.1145\/3589330"},{"key":"e_1_2_2_78_1","doi-asserted-by":"publisher","DOI":"10.14778\/3583140.3583160"},{"key":"e_1_2_2_79_1","doi-asserted-by":"publisher","DOI":"10.14778\/3461535.3461539"}],"container-title":["Proceedings of the ACM on Management of Data"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/10.1145\/3709659","content-type":"unspecified","content-version":"vor","intended-application":"text-mining"},{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.1145\/3709659","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2026,3,31]],"date-time":"2026-03-31T18:16:54Z","timestamp":1774981014000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.1145\/3709659"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2025,2,10]]},"references-count":79,"journal-issue":{"issue":"1","published-print":{"date-parts":[[2025,2,10]]}},"alternative-id":["10.1145\/3709659"],"URL":"https:\/\/doi.org\/10.1145\/3709659","relation":{},"ISSN":["2836-6573"],"issn-type":[{"value":"2836-6573","type":"electronic"}],"subject":[],"published":{"date-parts":[[2025,2,10]]}}}