Hide repeated joins
Give applications a stable, meaningful interface over complex SQL.
PART 4 • DATABASE SYSTEMS
Make Queries Faster for the Right Reason
Separate reusable query interfaces from stored results, choose useful access paths and read execution plans without guessing.
BY THE END, YOU CAN
- Compare views and materialized views.
- Explain B+ tree and hash indexes.
- Design composite indexes.
- Predict sequential and index scans.
- Read an execution plan.
- Follow a safe tuning workflow.
A View Stores a Definition, Not Ordinary Result Rows
BASE TABLESstudents + departments
→Current source data remains authoritative.
VIEW DEFINITIONCREATE VIEW active_students AS ...
→The database stores the named query.
QUERY THE VIEWSELECT * FROM active_students
The result reflects current underlying data.
Expose fewer columns
Grant access to an approved projection, while remembering that permissions and ownership rules vary by product.
Separate consumers
Shield reports from some schema details, but do not treat a view as a guaranteed compatibility layer for every change.
Not automatically faster
A normal view is expanded into the surrounding query; it usually does not cache rows.
A Materialized View Trades Freshness for Faster Reads
- Stores query definition
- No separate refresh
- Work is performed when queried
- Consumes storage
- Can be indexed
- Needs refresh to become current
10:00Refresh snapshot
10:30Base tables change
11:00Report may still show 10:00 data
Good fit: expensive summaries read frequently where controlled staleness is acceptable. Poor fit: transaction screens that must show the latest committed value.
An Index Reduces Search Work but Adds Maintenance Work
Ordered navigation
Supports equality, range predicates, ordered traversal and often prefix matching.
40 | 70
10 20 3040 50 6070 80 90
Equality lookup
Maps a key to a bucket. Excellent for supported equality cases, but generally unsuitable for range ordering.
0 → 24, 601 → 13, 492 → 08, 32
Answer from the index
When all required columns are available in the index and visibility rules permit, the engine may avoid fetching table pages.
INDEX (dept_id, salary) INCLUDE (name)READ BENEFIT fewer pages, faster filtering, useful orderingWRITE COST extra work on INSERT, UPDATE and DELETESPACE COST additional disk and cache usageDESIGN COST redundant indexes complicate maintenance
A Composite Index Is Not a Bag of Columns
INDEX KEY ORDER(department_id, salary, joined_at)
1
department_id2
salary3
joined_at
department_id2
salary3
joined_at
Useful starting patterns
department_id = ?department_id = ? AND salary > ?department_id = ? AND salary = ? AND joined_at > ?
Usually cannot seek efficiently from the left edge
salary > ?alonejoined_at = ?alone
Put frequently used equality columns before a range column when it matches real workload needs.
Columns after the first range may be less useful for narrowing the seek, though they can still help coverage.
An index on a low-cardinality flag may not save enough table work by itself.
Design from actual predicates, joins, ordering and returned columns—not from a universal formula.
A Sequential Scan Is Sometimes the Best Plan
Often best when a large fraction of rows is needed, the table is small, or no useful index exists.
Often best for selective predicates, but scattered table lookups become expensive when many rows qualify.
Possible only when the index covers the query and database visibility conditions allow it.
Some products use bitmap plans between highly selective index access and a full sequential scan.
WHERE created_at >= DATE '2026-01-01'The indexed column remains directly comparable.
WHERE EXTRACT(YEAR FROM created_at) = 2026A normal index on created_at may be harder to use unless a matching expression index exists.
EXPLAIN Shows a Tree of Physical Operations
An optimizer estimate in product-specific units—not elapsed milliseconds.
Estimated output cardinality. Large estimate-versus-actual errors can cause poor choices.
How many times an operation ran in an actual plan. Multiply per-loop work when interpreting totals.
Estimated average bytes per output row, which affects memory and I/O estimates.
EXPLAIN versus EXPLAIN ANALYZE
EXPLAIN estimates without executing in common systems. EXPLAIN ANALYZE actually runs the statement and reports measurements. Use it cautiously with writes or expensive production queries; product syntax and behavior vary.
A Responsible Optimization Workflow
- 1Measure the real workload
Capture slow, frequent or resource-heavy statements with representative parameters.
- 2Inspect the plan
Locate expensive nodes, large row flows, repeated loops and estimate errors.
- 3Check query shape and data
Review predicates, joins, selected columns, statistics and data distribution.
- 4Make one justified change
Rewrite a predicate, update statistics or add the smallest useful index.
- 5Measure again
Compare latency, reads, writes, storage and plan stability under representative load.
- 6Monitor side effects
Confirm that other queries and write operations did not regress.
Interactive Index Design Advisor
Select a workload and compare candidate indexes. The explanation includes benefits, limitations and write cost.
WORKLOAD QUERY
Execution Plan Explorer
Change table size, expected matches and index availability. This simplified cost model teaches the optimizer's trade-off; it is not a real database plan.
Read table pages in order.
Educational model: actual optimizers use product-specific costs, statistics, page layout, caching, parallelism and many other factors.
Ten Formative Concept Checks
1. A normal SQL view primarily stores:
2. A materialized view normally needs what to reflect later base-table changes?
3. Which index structure naturally supports ordered range lookup?
4. For index (department_id, salary), which predicate best uses its left edge?
5. Adding an index generally increases the cost of:
6. A sequential scan can be optimal when:
7. In a query plan, estimated cost is usually:
8. A large estimated-versus-actual row difference often suggests:
9. Which predicate is more search-friendly for a normal index on created_at?
10. The safest first step in query tuning is to:
Answered correctly: 0 of 10
University and Interview Questions
- Define a view.
- What is selectivity?
- B+ tree versus hash?
- What does EXPLAIN show?
- Compare views and materialized views.
- Design a composite index for a query.
- Explain sequential versus index scans.
- Interpret a plan tree.
- Why might a database ignore an index?
- Can too many indexes hurt?
- How do stale statistics affect plans?
- How would you verify an optimization?
Show the optimization answer framework
- State the workload and required result.
- Identify filters, joins, ordering and output columns.
- Estimate selectivity and row flow.
- Describe candidate access paths.
- Read the plan from leaves toward the root.
- Compare estimated and actual rows where safe.
- Measure the change and its read/write trade-offs.
LEVEL 16 SUMMARY
You Can Now Optimize with Evidence
- Views store definitions; materialized views store refreshable results.
- Indexes trade write and space cost for useful access paths.
- Composite-index column order follows workload patterns.
- Sequential scans are correct choices for many-row reads.
- Plans expose estimated operations and row flow.
- Measure before and after every tuning change.
COURSE CHECKPOINT
Mark this level when you can justify an access path using workload, selectivity and plan evidence.
Saved in this browser only.