SQL query optimization with EXPLAIN
SQL query optimization starts with a correct answer and a plan you can inspect. Record the query, the input tables and the result before changing anything. A faster query that drops customers or counts a refund twice answers a different question.
Estimated and measured plans
EXPLAIN shows the planned operators and estimated row counts. EXPLAIN ANALYZE runs the query and adds execution evidence. In PostgreSQL, a node’s actual rows and time are averages per execution when loops is greater than one. Read those fields together. Planner cost is not elapsed time in milliseconds.
Follow the inputs through scans, joins and aggregation. Find where the row count grows, then check the join keys against the intended result grain. A large intermediate result can explain why a query does more work than its final row count suggests.
Rewrites and correctness
Compare one change at a time. When you need customers with an order, an EXISTS condition expresses that requirement without returning one customer row for every matching order. When you need order totals, aggregate at order grain before joining several detail tables. Keep the treatment of missing matches and NULL values explicit.
Use the safe rewrite exercises to test those decisions. The refund-report project carries your query through several steps, so you can explain both the result and the change you made.
Engine boundaries
The browser workspace uses DuckDB. Its plan operators, optimizer and index behavior differ from PostgreSQL, MySQL and SQL Server. A small browser exercise teaches the diagnosis; its timing is not a production benchmark. Use the target engine and a representative workload to measure a deployment decision.
Continue with SQL index practice. Database roles, backups, maintenance and server administration are covered in the PostgreSQL administration documentation.
References: PostgreSQL EXPLAIN, DuckDB EXPLAIN.