Interview Q&A / PostgreSQL

1. What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read a query plan?

   EXPLAIN shows the plan PostgreSQL intends to use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually runs the query and adds real timings and real row counts, so you can compare estimates with reality.

   BeginnerIn-depth4 min read
2. Is it safe to run EXPLAIN ANALYZE on an UPDATE or DELETE?

   No, not without care. EXPLAIN ANALYZE really executes the statement, so the rows are changed. Wrap it in BEGIN and ROLLBACK to inspect the plan and timings without keeping the change.

   BeginnerQuick answer1 min read
3. What do the cost numbers in an EXPLAIN plan actually mean?

   cost=A..B gives the estimated startup cost (before the first row) and total cost, in arbitrary planner units based on page fetches and CPU work. They are for comparing plans, not seconds or milliseconds.

   BeginnerQuick answer1 min read
4. What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan in a PostgreSQL plan?

   Seq Scan reads the whole table, Index Scan follows the index row by row, and Bitmap Heap Scan collects matching row locations from the index first, then reads table pages in physical order. The planner picks by estimated selectivity and cost.

   BeginnerQuick answer1 min read
5. What are the three join algorithms in PostgreSQL and when does the planner choose each?

   Nested Loop probes the inner side once per outer row and suits small inputs or indexed lookups. Hash Join builds a hash table of one side and suits large unsorted equality joins. Merge Join walks two sorted inputs and suits large sorted ones.

   IntermediateQuick answer1 min read
6. What does EXPLAIN (ANALYZE, BUFFERS) add and what do shared hit and read mean?

   BUFFERS reports how many 8 kB pages each plan node touched. shared hit means the page was already in PostgreSQL's cache, shared read means it came from the operating system or disk. It shows I/O cost that timing alone hides.

   IntermediateQuick answer1 min read
7. What does it mean when estimated rows and actual rows differ a lot in EXPLAIN ANALYZE?

   The planner chose its plan from a wrong guess about row counts. Usual causes are stale statistics, correlated columns, or expressions it cannot estimate. Run ANALYZE, raise the statistics target, or create extended statistics.

   IntermediateQuick answer1 min read