Query Optimization Discussion — Database & SQL Exercises

1 / 5

A senior DBA says in a performance review meeting: "The query is doing a Seq Scan on a 4-million-row table — that's why it's slow." What does Seq Scan mean, and why is an Index Scan preferred for selective queries?

EXPLAIN ANALYZE node type vocabulary:

Option A is the correct definition. A Sequential Scan is not inherently bad — for queries that return a large percentage of rows, it can be faster than an Index Scan because it reads pages sequentially (which is cache-friendly). The problem arises when a Seq Scan occurs on a large table with a highly selective WHERE clause.

Scan type	How it works	Best when
Seq Scan	Reads every page of the table in order	Query returns >10–20% of rows; no suitable index
Index Scan	Traverses the index, then fetches heap pages	Query is selective (<5–10% of rows); index exists
Index Only Scan	Reads only the index; never touches the heap	All projected columns are in the index (covering index)
Bitmap Index Scan	Builds a bitmap of matching heap pages, then reads them	Moderate selectivity; reduces random I/O

How to communicate scan types in reviews: "The planner chose a Seq Scan here because there is no index on the status column and the filter is not selective enough. Adding a partial index on WHERE status = 'pending' would allow an Index Scan and reduce cost significantly."

2 / 5

A code reviewer leaves this comment on a pull request:

"This code has an N+1 problem. We'll issue one query to fetch all posts, then a separate query per post to fetch its author — that's 1 + N queries for N posts."

Which code pattern is the reviewer describing, and what is the standard vocabulary for the solution?

N+1 problem vocabulary:

Option D is the complete and correct description. The N+1 pattern is one of the most common performance anti-patterns in ORM-heavy applications and a standard topic in backend code reviews.

Example — N+1 in code:

// N+1: 1 query for posts, then 1 per post for author
const posts = await Post.findAll();          // 1 query
for (const post of posts) {
  const author = await User.findById(post.authorId); // N queries
}

Solution vocabulary:

Term	Meaning
Eager loading	Load associated data up-front, in the same query (JOIN) or a second batched query
Lazy loading	Load associated data on demand as it is accessed — causes N+1
Batched query / DataLoader pattern	Collect all IDs from the loop, then run one IN (...) query to fetch all at once
JOIN fetch	A single query with a JOIN that loads both entities simultaneously

How to phrase the review comment professionally: "This will generate one query per post to load the author relationship — that's N+1. Consider using eager loading (include: ['author']) or a JOIN, which collapses this into a single round trip."

3 / 5

A DBA explains a EXPLAIN output to a junior developer: "The cost estimate shows 8,924.00 — that's the planner's estimate of total cost units, not milliseconds." Which statement correctly explains PostgreSQL's COST value?

PostgreSQL query plan cost vocabulary:

Option B is correct. The COST value in EXPLAIN output is a dimensionless unit, not milliseconds. It is computed from configurable constants that abstract disk I/O and CPU operations.

Key cost constants (postgresql.conf):

Parameter	Default	What it models
`seq_page_cost`	1.0	Cost of fetching one page via sequential read
`random_page_cost`	4.0	Cost of fetching one page via random read (index access)
`cpu_tuple_cost`	0.01	Cost of processing one row
`cpu_index_tuple_cost`	0.005	Cost of processing one index entry

EXPLAIN output reading vocabulary:

cost=X..Y — X is startup cost (cost before first row returned); Y is total cost
rows=N — planner's estimate of rows returned; compare with actual rows after EXPLAIN ANALYZE
width=N — estimated average row width in bytes
actual time=X..Y — measured execution time in milliseconds (only with ANALYZE)

In performance discussions, always say "cost units" or "estimated cost" — never "the query costs 8,924 milliseconds" when reading plan output without ANALYZE timing.

4 / 5

A DBA reports during a performance review: "The BUFFERS output shows a 98% buffer hit rate — this query is well-cached." What does a buffer hit indicate in the context of a PostgreSQL query plan?

Buffer hit vocabulary in EXPLAIN BUFFERS output:

Option C is correct. PostgreSQL's shared_buffers is an in-memory cache of database disk pages. When a query requires a page, it first checks shared_buffers; if found (a hit), no disk read is needed. If not found (a miss), the page must be fetched from disk (or the OS page cache).

BUFFERS output vocabulary:

Term	Meaning
Buffers: shared hit=N	N pages served from shared_buffers (no disk I/O)
Buffers: shared read=N	N pages read from disk into shared_buffers (disk I/O occurred)
Buffers: shared written=N	N dirty pages flushed to disk by this query
Buffer hit rate	hit / (hit + read) × 100 — higher is better; >90% is generally healthy

How to use buffer hit data in performance discussions:

A low hit rate (e.g., 40%) on a frequently-executed query suggests the working set exceeds shared_buffers — consider increasing it or adding a covering index to reduce the number of pages accessed.
A 100% hit rate does not mean the query is free — CPU processing of many cached pages can still be expensive.
Buffer hits persist across queries; the first cold run of a query will always show more reads than subsequent warm runs.

5 / 5

An architect says in a query review: "We should add a covering index so the planner can use an Index Only Scan instead of an Index Scan." What does an Index Only Scan avoid that a regular Index Scan does not?

Index Only Scan vocabulary:

Option B is the correct definition. A regular Index Scan must make a second I/O round trip from the index entry to the corresponding heap page to fetch the projected columns. An Index Only Scan eliminates this by storing all projected columns directly in the index — called a covering index.

Scan type	Index read?	Heap read?	When possible
Index Scan	Yes	Yes (per row)	Index exists on filter column(s)
Index Only Scan	Yes	No (usually)	Covering index includes all SELECTed columns
Seq Scan	No	Yes (all pages)	No suitable index; high row selectivity

Covering index vocabulary:

Covering index — an index that includes all columns needed to satisfy a query, enabling Index Only Scan
INCLUDE clause — PostgreSQL 11+ syntax to add non-key columns to the index leaf pages: CREATE INDEX idx ON orders (status) INCLUDE (total, created_at)
Heap fetch — the secondary I/O step where a regular Index Scan reads the actual table page to retrieve projected columns not in the index
Visibility map — PostgreSQL checks the visibility map before doing an Index Only Scan; if not all pages are marked all-visible, heap fetches occur anyway