[ TOPIC ]
query-optimization
16 real merged PRs across 4 projects, ordered easiest to hardest. Read them in order to see how query-optimization shows up in production code, then go deeper with the free papers and talks below.
[ 01 // REAL PRs ]
Next (mid) · 6
fix: support vector output with order_by
This PR teaches how to handle late materialization of complex data types (like vectors) in query execution plans to support efficient ORDER BY queries with vector outputs.
Fixes (#24115) : Use single-column ART index scan for IS NOT DISTINCT FROM
This PR teaches how to extend an index scan optimization to support SQL's IS NOT DISTINCT FROM, including handling of NULL semantics and verifying correctness with targeted tests.
Prune row groups for column-constant OR
This PR teaches how to optimize query execution by pruning row groups using cross-column OR predicates and demonstrates careful test coverage and code review for correctness and maintainability.
Prune row groups when an IN-list covers the whole zonemap rangend add
This PR teaches how to use zonemap statistics and IN-list analysis to optimize query execution by pruning unnecessary row group scans, including careful handling of edge cases and robust testing.
Fix statistics propagation for window aggregate functions (#23663)
This PR teaches how to correctly propagate statistics for window aggregate functions in a query optimizer, including how to structure callbacks and tests for stats-dependent aggregates.
fix: validate MATCH predicates are element-level [3.0]
This PR demonstrates how to robustly validate and test element-level match predicates in a query engine, ensuring correctness and preventing subtle errors in predicate evaluation.
Go deep (senior) · 10
Fix RIGHT JOIN with parallel_replicas_min_number_of_rows_per_replica
This PR teaches how to correctly handle query plan analysis reuse in parallel query execution, especially for RIGHT JOINs, to avoid subtle cross-table errors and demonstrates robust regression testing for distributed SQL engines.
Fix wrong `ORDER BY` results for `Merge` over `Distributed`
This PR teaches how distributed query engines must carefully preserve stream ordering to ensure correct results for ORDER BY and LIMIT, and how to write regression tests for subtle distributed query bugs.
Add multiplicity-aware combine support and fix LIST state combination
This PR teaches how to correctly handle aggregate state combination with multiplicities in query optimizations, especially for list-backed aggregates, and demonstrates robust testing and plan stability practices.
planner: support TiFlash MPP full outer join
This PR demonstrates how to extend a distributed SQL planner to support full outer joins in MPP (Massively Parallel Processing) mode, including join type validation, plan enumeration, and serialization for execution engines like TiFlash.
planner: fix countAfterAccess for IndexJoin in Skyline Pruning Comparison (#70791)
This PR teaches how to accurately estimate row counts for index join candidates in a query planner by adjusting for join key NDV, ensuring fair comparison and optimal index selection during skyline pruning.
planner: keep min/max row estimates aligned after correlated column split (#70793)
This PR demonstrates how to maintain consistent row estimate bounds for correlated index paths during query optimization to avoid incorrect pruning, including the reasoning and regression test coverage needed for robust planner changes.
planner: fix countAfterAccess for IndexJoin in Skyline Pruning Comparison
This PR teaches how to adjust row count estimation for index join selection in a query optimizer by accounting for join key NDV, ensuring fairer candidate comparison and demonstrating robust regression testing for plan choice correctness.
Fix missing materialized-CTE gate when a `Merge` table has several children
This PR teaches how to safely manage and optimize materialized CTE ownership across multiple query plan branches to prevent subtle concurrency and correctness bugs in complex query engines.
Fix wrong results with parallel_hash JOIN and read-in-order-through-join
This PR teaches how to reason about and enforce data stream order preservation through different join algorithms to ensure correctness of downstream query optimizations that rely on sorted input.
Fix Top-N window elimination for nullable ordering expressions
This PR teaches how to conservatively analyze SQL expression nullability to ensure correctness in query optimization, especially when rewriting window functions with potentially nullable ordering expressions.
[ 02 // MUST-READ PAPERS ]
Free to read, ranked by citations.
- Access path selection in a relational database management systemP. Selinger, M. M. Astrahan, D. Chamberlin · ACM SIGMOD Conference · 1979
This paper describes how System R chooses access paths for both simple (single relation) and complex queries (such as joins) given a user specification of desired data as a boolean expression of predicates.
2,558 citationsfree PDF - How Good Are Query Optimizers, Really?Viktor Leis, Andrey Gubichev, Atanas Mirchev · Proceedings of the VLDB Endowment · 2015
This paper introduces the Join Order Benchmark (JOB) and experimentally revisit the main components in the classic query optimizer architecture using a complex, real-world data set and realistic multi-join queries.
910 citationsfree PDF - Efficiently Compiling Efficient Query Plans for Modern HardwareThomas Neumann · VLDB · 2011512 citationsfree PDF
[ 03 // FREE VIDEOS & TALKS ]
Ranked by views.
other topics