Actor: PostgreSQL / MySQL · Connection pool → query planner → index scan → buffer pool
The app server picks a connection from the connection pool (pre-opened DB connections — opening a fresh one costs ~50ms). The DB parses the query, checks for a cached execution plan, runs the query optimizer, and executes using the most efficient index available.
-- App sends:
SELECT id, title, content FROM pages
WHERE slug = 'home' AND published = true;
-- Query plan (EXPLAIN):
Index Scan using idx_pages_slug on pages
Index Cond: (slug = 'home')
Filter: (published = true)
Planning time: 0.12 ms
Execution time: 0.38 ms
🗄️ SQL QUERY EXECUTION PIPELINE
① PARSER
Tokenize and build a parse tree
Receives the raw SQL string. Breaks it into tokens (SELECT, id, FROM, pages…), checks syntax, and builds an internal parse tree. Rejects queries with syntax errors here before any data is touched.
② PLANNER
Check table statistics and available indexes
Looks at pg_stats for the pages table: 5,000 rows, 95% distinct slug values. Sees that idx_pages_slug exists. Considers possible strategies: sequential scan, index scan, or bitmap heap scan.
③ OPTIMIZER
Pick the cheapest execution plan
Calculates cost of each strategy in arbitrary units. Sequential scan (read all 5,000 rows): cost=89. Index scan (jump directly to matching rows): cost=0.3. Index scan wins. Creates the final execution plan.
④ INDEX SCAN
B-Tree traversal on idx_pages_slug
Traverses the B-Tree index, comparing key="home" down the tree. 3 node comparisons to find the leaf. Gets a pointer (tuple ID = page 42, row 7) pointing to the actual data on disk. Lightning fast — O(log n).
⑤ BUFFER
POOL
Check shared memory before hitting disk
The Buffer Pool (shared_buffers) is PostgreSQL's in-memory page cache. Checks if page 42 is already loaded. Cache hit — the page is in memory. No disk I/O needed. Fetches the row directly from RAM in microseconds.
⑥ RESULT
1 row returned in 0.38ms total
Applies the WHERE filter (published = true), projects the requested columns (id, title, content), and returns the result set to the app server via the connection pool socket.
"Query executed via index scan. 1 row returned in 0.38ms. Sending result back to app server via connection pool."