Query Tuning for PostgreSQL DBAs
Query tuning is finding why a query is slow and fixing it, mainly by reading execution plans and choosing indexes. EXPLAIN ANALYZE shows scans, joins, row estimates and timings, while indexes such as B-tree, GIN and partial indexes cut the work done. It is the first performance stage of the PostgreSQL DBA path.
Stage: Performance · Part of the PostgreSQL DBA roadmap · Last reviewed 19 November 2026
What to learn in Query Tuning
- Read an EXPLAIN (ANALYZE, BUFFERS) plan and find the slowest node
- Spot bad row estimates and fix them with ANALYZE or extended statistics
- Create a composite index with column order that matches the query
- Choose between B-tree, GIN, GiST and BRIN indexes for different data
- Use pg_stat_statements to find the most expensive queries
Query Tuning interview questions
A plan shows a sequential scan on a large table although an index exists. Why might that be?
The planner may judge the scan cheaper because the query returns a large fraction of rows, statistics are stale, the predicate does not match the index (a function on the column or a type mismatch), or the table is small. Check with EXPLAIN, run ANALYZE and test the predicate.
When is a partial index useful?
When queries target a small, well-defined subset, such as WHERE status = 'pending' on a table that is mostly completed rows. The index covers only matching rows, so it is smaller, cheaper to maintain and more likely to stay in memory.
Where Query Tuning fits in the PostgreSQL DBA roadmap
Query Tuning belongs to the Performance stage (intermediate level), which takes about 7 hours. Make it fast.
Other units in this stage
Back to the full PostgreSQL DBA roadmap