การอ่านผลลัพธ์ EXPLAIN และ EXPLAIN ANALYZE

Before tuning a slow query, see what PostgreSQL actually does. EXPLAIN shows the planner's chosen execution plan as a tree of nodes.

EXPLAIN shows estimates only; EXPLAIN ANALYZE actually runs the query and reports real timings and row counts — far more useful for diagnosis.

Read plan trees inside-out and bottom-up: leaf nodes scan data, parents join or aggregate. Each node reports its cost, rows, and width.

The cost reads as cost=0.00..18.45: first is startup cost, second is total, both in arbitrary planner units used to compare competing plans.

A Seq Scan reads the whole table — fine for small tables or when most rows match, but a red flag on a selective query over a large one.

An Index Scan uses an index to find matching rows, then fetches them from the table. It's the win for selective predicates.

Compare estimated rows with actual rows. A big mismatch means stale statistics and often a bad plan — run ANALYZE to refresh them, as shown below.

To find the bottleneck, hunt for the node with the largest actual time, or one whose loops multiply its cost. That's where to focus tuning.

Add BUFFERS to your EXPLAIN to see how many pages came from cache versus disk — the fast way to spot I/O-bound queries. See the example below.

Watch the join strategy: Nested Loop, Hash Join, or Merge Join. A Nested Loop over many rows without an index is a classic slow pattern.

For big plans, switch to FORMAT JSON or a visual tool to explore them more easily. The query below shows the JSON option.

Recap: read EXPLAIN trees, compare estimates with ANALYZE actuals, tell Seq from Index scans and join types, use BUFFERS, and find the bottleneck node.