EXPLAIN vs EXPLAIN ANALYZE?

EXPLAIN shows the estimated plan (based on statistics). EXPLAIN ANALYZE actually executes the query and shows actual row counts, timing, and memory usage. Use EXPLAIN for quick checks; EXPLAIN ANALYZE for accurate diagnosis. Note: ANALYZE executes the query — for INSERT/UPDATE/DELETE, use a transaction with ROLLBACK.

What is a sequential scan and when is it bad?

A sequential scan reads every row in the table. On small tables or when most rows match, it is fine (faster than index lookup + fetch). On large tables with few matching rows, it is slow — add an index. The EXPLAIN output shows "Seq Scan" — check if the estimated rows are a large fraction of the table.

Analyser SQL requête exécution plans

EXPLAIN affiche comment le base de données executes un requête. Apprendre en lire plans, identifier goulots d’étranglement, et optimiser.

1

EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL, EXPLAIN WITH STATISTICS in MySQL) shows the execution plan: scan types (sequential, index, bitmap), join types (nested loop, hash, merge), sort methods, estimated costs and rows. Look for: sequential scans on large tables, expensive sorts, nested loop joins on large inputs, inaccurate row estimates.
2

Formateur SQL →
Formater the query before running EXPLAIN. A readable query makes it easier to map the execution plan back to the SQL. Format also helps identify which parts of the query are causing the expensive operations in the plan.
3

sql-format → word-count
Voir la recette Ouvrir dans la chaîne d’outils

Use the SQL format workflow to format and document the query, then run EXPLAIN on the formatted version. This produces a clean query that is easy to correlate with the execution plan.

Questions fréquentes

EXPLAIN vs EXPLAIN ANALYZE?: EXPLAIN shows the estimated plan (based on statistics). EXPLAIN ANALYZE actually executes the query and shows actual row counts, timing, and memory usage. Use EXPLAIN for quick checks; EXPLAIN ANALYZE for accurate diagnosis. Note: ANALYZE executes the query — for INSERT/UPDATE/DELETE, use a transaction with ROLLBACK.
What is a sequential scan and when is it bad?: A sequential scan reads every row in the table. On small tables or when most rows match, it is fine (faster than index lookup + fetch). On large tables with few matching rows, it is slow — add an index. The EXPLAIN output shows "Seq Scan" — check if the estimated rows are a large fraction of the table.