Skip to main content

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

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.

Back to the full PostgreSQL DBA roadmap