Database field guide · 06
The Life of a SQL Statement
Parse, plan, execute, observe, repeat
Follow SQL from text and binds through parsing, optimization, execution, caching, invalidation, and runtime evidence.
Franck Pachot8 chaptersparsing · optimization · execution
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
Text becomes a database object
Before execution, SQL text is parsed, names are resolved, privileges checked, datatypes inferred, and dependencies recorded. Small textual differences can create different cache identities even when humans read the statements as equivalent.
- Use bind variables for values, not identifiers or syntax.
- Qualified names and search paths influence dependency resolution.
- Parsing is both semantic work and shared-cache coordination.
02
The optimizer builds alternatives
The parsed tree is transformed into equivalent relational forms. The optimizer estimates rows and costs for scans, joins, aggregation, sorting, and data movement, then selects an executable plan. Its output depends on statistics, parameters, schema, and available physical structures.
- Optimization is constrained search, not exhaustive proof.
- Cardinality errors propagate into later choices.
- A plan is valid for a context, not universally optimal.
03
Binds delay knowledge
Bind variables improve sharing and security but hide values until execution. Oracle bind peeking and adaptive cursor sharing, and PostgreSQL custom versus generic plans, are different responses to the same tension: one cached plan may not fit every value distribution.
- Capture the parameter values behind a slow execution.
- Separate parse-time estimates from runtime adaptation.
- Skewed workloads may need deliberate statement variants.
04
Execution turns operators into work
Plan nodes request rows from their children, allocate memory, read buffers, acquire locks, produce temporary data, and return batches to the client. Fetch size and client behavior can make a fast server plan appear slow or leave much of a plan unexecuted.
- Distinguish startup time from total time.
- Observe buffers, rows, waits, spills, and network fetches.
- A LIMIT can stop lower nodes before their estimated total work.
05
Caching avoids repeated planning
A reusable cursor or prepared plan saves parse and optimization work. It also carries dependencies and assumptions that can become stale. DDL, statistics changes, configuration, and memory pressure can invalidate or age cached objects.
- A soft parse is cheaper but not free.
- Hard-parse storms are both CPU and concurrency problems.
- Do not flush a whole cache to diagnose one statement.
06
Stability needs controlled change
Plan baselines and related mechanisms constrain which plans may be selected; they do not repair bad estimates or missing access paths. Use them as operational guardrails while preserving the evidence needed for a root-cause fix.
- Record plan identity with execution statistics over time.
- Test candidate plans with representative binds.
- Allow a path for verified improvements to replace old baselines.
07
Observe the complete path
A useful trace connects application request, SQL identity, parse activity, chosen plan, waits, row counts, commit, and client fetch. Looking at only the final duration collapses distinct problems into one number.
- Tag sessions and requests with meaningful module metadata.
- Correlate database time with application and network time.
- Keep a minimal reproducible execution with schema and binds.
08
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Separate parse, bind, execute, and fetch
Parsing validates syntax and resolves names; binding supplies typed values; execution opens the plan and produces rows; fetching transfers result batches. Database time can be small while application latency is large if a client fetches fifteen rows per network round trip. Conversely, a cursor may execute but never run expensive lower nodes when the client stops early. Trace the phases independently.
-- PostgreSQL server-side preparation
PREPARE recent_orders(bigint) AS
SELECT * FROM orders WHERE customer_id = $1
ORDER BY created_at DESC LIMIT 20;
EXECUTE recent_orders(42);
Understand cursor identity
Oracle groups cursor children under SQL_ID but may create children for optimizer environment, bind metadata, authorization, or adaptive cursor sharing. PostgreSQL prepared statements live in a session, while pg_stat_statements groups normalized query shapes globally. SQL text identity, plan identity, and business operation identity are different dimensions; keep all three in telemetry.
SELECT sql_id, child_number, plan_hash_value, executions,
parse_calls, invalidations, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;
Read execution as an iterator pipeline
Most row-source plans use a demand-driven iterator model: a parent asks a child for the next row. Startup cost describes work before the first row; total cost assumes all rows are consumed. Blocking operators such as sort or hash aggregation may consume their input before returning anything, while an index scan under LIMIT can stop immediately. This explains why first-row latency and total throughput favor different plans.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM event
WHERE tenant_id = 7
ORDER BY event_time DESC
LIMIT 1;
Treat invalidation as dependency maintenance
DDL, statistics refresh, privilege changes, search path, and optimizer settings can make a cached plan unusable or inappropriate. Replanning is correct behavior, but synchronized invalidation of a hot statement can produce a parse storm. Roll out DDL with lock timeouts, observe parse rates and library/cache contention, and avoid clearing an entire cache to change one statement.
SELECT queryid, calls, plans, total_plan_time, total_exec_time
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_plan_time DESC
LIMIT 20;
09
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
