The Art of SQL Query Optimization

a year ago

SQL is a declarative language where only the query result is specified, and the DBMS determines the steps to produce it.
The query optimizer finds the most efficient plan by generating and evaluating possible plans based on costs.
Plan Explorer is a tool that visualizes PostgreSQL query optimizer decisions by iterating over a search space and generating diagrams.
The tool can operate in standalone mode using WebAssembly PostgreSQL (PGlite) or in server mode to connect to real PostgreSQL installations.
Plan Explorer generates visualizations of query plans, expected costs, actual execution times, and estimated vs. actual tuple counts.
Example query analysis shows PostgreSQL's optimization, such as changing a LEFT JOIN to an INNER JOIN when filters eliminate NULL tuples.
The tool helps identify mispredictions, like incorrect tuple count estimates, and aids in tuning cost models.
Plan Explorer is open-source and available on GitHub, providing insights into PostgreSQL's query optimization decisions.

Hasty Briefsbeta