How a SQL query runs, drawn

Quick reference

• Client: driver (DRV) · connection pool (POOL)
• Engine: parser (PARSE) · planner (PLAN) · executor (EXEC)
• Storage: buffer cache (CACHE) · heap files (HEAP) · indexes (IDX) · WAL (WAL)

Request flow: driver → connection pool → parser → planner → executor → buffer cache → heap files; buffer cache → indexes; executor → WAL

Explained

Client The driver speaks the database protocol, binds your parameters and returns typed rows. A connection pool keeps a handful of physical connections open, so the next query does not pay for TCP, TLS and authentication again.

Engine The parser turns the text into a syntax tree and checks it against the system tables; a typo fails here. The planner estimates how many rows each step will touch, using the statistics the last ANALYZE collected, then chooses a seq scan or an index, a nested loop or a hash join. The executor pulls rows through that plan, one node at a time, and asks the buffer cache for pages.

Storage The buffer cache holds hot 8 kB pages in shared memory. A miss reads from the heap files that hold the table, or from an index, and a write first appends to the WAL so a crash can replay it. The heap is where the rows live; the index is only a map to them.

In one line

Read the plan

EXPLAIN ANALYZE SELECT * FROM t;