writing-performant-querieslisted

# Writing Performant Queries ## Required diagnostic sequence Follow these steps in order. If the user already provides the query and plan, start at step 2. **Never propose an index before identifying which query is slow and inspecting evidence from its plan.** ### 1. Find the expensive query Use `pg_stat_statements` to rank normalized SQL before tuning anything. `total_exec_time` finds aggregate database load; `mean_exec_time` finds individually slow calls. Compare a defined time window and note when statistics were reset. ```sql SELECT queryid, calls, total_exec_time, mean_exec_time, rows, left(query, 200) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` If it is not installed, add `pg_stat_statements` to the existing comma-separated `shared_preload_libraries`, restart PostgreSQL, and create the extension in each database. Do not guess from a generic “the API is slow” report. ### 2. Inspect that query's plan safely Use plain `EXPLAIN` first; it plans but does not execute the statement. Use `EXPLAIN (ANALYZE, BUFFERS)` only when executing the query is safe and representative. **Warning: `EXPLAIN ANALYZE` executes the statement. Never run it casually on a production `INSERT`, `UPDATE`, or `DELETE`; it performs writes, takes locks, and can trigger side effects.** Prefer staging or a safe read-only reproduction for write queries. ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tasks WHERE tenant_id = $1 AND status = 'pending' ORDER BY