LLM Best Practices

Query Plan

2 min read

View markdown source

Definition

A query plan is the tree of operations the database engine will execute to satisfy a SQL statement. In PostgreSQL, EXPLAIN shows the estimated plan; EXPLAIN ANALYZE runs the query and shows actual row counts, timing, and loop iterations. Each node in the tree is a plan node: Seq Scan, Index Scan, Index Only Scan, Hash Join, Nested Loop, Sort, Aggregate, etc.

The query planner chooses a plan by estimating the cost of each candidate strategy using table statistics (collected by ANALYZE). Cost estimates have two components: startup cost (work done before the first row is returned) and total cost. The planner picks the plan with the lowest estimated total cost.

Key plan nodes:

  • Seq Scan: reads every row; cheap for small tables or low-selectivity queries.
  • Index Scan: follows the index, then heap-fetches each matching row.
  • Index Only Scan: reads from the index without heap access; requires a covering index and up-to-date visibility map.
  • Bitmap Heap Scan: combines multiple index scans, then fetches heap pages in physical order.
  • Hash Join / Merge Join / Nested Loop: three join strategies with different trade-offs on row count and sort order.

When it applies

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on any slow query before adding an index. Check estimated vs. actual row counts: a large discrepancy means stale statistics; run ANALYZE tablename. Check Buffers: shared hit vs. shared read; high read means the working set does not fit in shared_buffers.

Example

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles
WHERE author_id = 42
  AND published_at > now() - interval '30 days'
ORDER BY published_at DESC;

If the plan shows Seq Scan on a large articles table, consider a composite index on (author_id, published_at DESC). After adding the index, re-run EXPLAIN ANALYZE to confirm Index Scan is chosen.

  • btree-index - the most common index type; the planner prefers it for equality and range queries.
  • sequential-scan - the fallback scan method when no index is useful.
  • gin-index - index for full-text and JSONB containment queries.
  • postgres - the Postgres deep-dive.
  • postgres-explain - the EXPLAIN cheatsheet with node annotations.

Citing this term

See Query Plan (llmbestpractices.com/glossary/query-plan).

Graph View

Backlinks