query-optimization

Diagnose a slow SQL query by running EXPLAIN ANALYZE to inspect the execution plan, identifying the most expensive plan node, and comparing estimated versus actual cardinalities to locate the root cause of the performance issue.

A Seq Scan plan node in EXPLAIN ANALYZE output indicates the database performs a sequential scan reading the entire table, which often causes high runtime costs compared to an Index Scan or Bitmap Heap Scan when filtering large datasets.

Fix bad cardinality estimation by refreshing or raising statistics with ANALYZE, adjusting statistics targets, or creating extended statistics to provide the query planner accurate data distribution for generating faster execution plans.

Decide between rewriting SQL or adding an index by examining the execution plan node: if the planner picks the correct access path but the query structure is inefficient, rewrite it; if it uses a Seq Scan where an Index Scan fits, add an index.

Verify SQL query optimization improvements by re-running EXPLAIN ANALYZE after applying your corrective action, comparing the new execution plan runtime statistics and costs against the original baseline to confirm the performance gain.

Nested Loop plan nodes execute efficiently for small datasets by iterating rows, while Hash Join nodes build a hash table for larger inputs, and misestimation by the planner between them causes severe SQL performance degradation.