HippoCampus
Technology & ComputingGuidedAdvanced20 chapters

Database indexes and query plans for backend developers

HippoCampus
· starter course
I'm a backend developer and want to understand database indexes properly: B-trees, composite and covering indexes, reading query plans, and diagnosing slow queries in PostgreSQL.

What you'll learn

20 chapters
  1. 1

    Performance Framing: Why Indexes and Query Plans Matter

    • Frame query performance in terms of access paths, cardinality, I/O, CPU, and memory rather than vague notions of slowness.
    • Differentiate developer-visible symptoms from database-internal causes when diagnosing slow queries.
    • Establish the PostgreSQL-specific scope of the course while identifying concepts that generalize across relational databases.
  2. 2

    Physical Storage Foundations in PostgreSQL

    • Explain how PostgreSQL heap storage, pages, tuples, and row identifiers constrain and enable index behavior.
    • Relate MVCC and tuple visibility to the real cost of using an index.
    • Use physical storage concepts to reason about heap fetches, table scans, and index-only scans later in the course.
  3. 3

    B-Tree Fundamentals and Internal Mechanics

    • Describe how PostgreSQL B-tree indexes organize keys and navigate to matching entries.
    • Analyze the consequences of page splits, tree height, fill behavior, and duplicate values on read and write performance.
    • Connect B-tree structure to common backend query patterns such as equality lookup, range filtering, and ordered retrieval.
  4. 4

    From Predicates to Access Paths: How Queries Become Indexable

    • Determine whether a SQL predicate can be supported efficiently by a given index structure.
    • Recognize common reasons a query is not sargable in practice, especially in PostgreSQL-backed applications.
    • Translate application query patterns into indexable search conditions.
  5. 5

    Single-Column Index Design and Selectivity

    • Design and evaluate single-column indexes based on selectivity, workload shape, and read-write tradeoffs.
    • Interpret why an index on a low-cardinality or highly common value may underperform expectations.
    • Use selectivity and result-set size to predict when sequential scans remain preferable.
  6. 6

    Composite Indexes: Left Prefix, Ordering, and Multi-Condition Queries

    • Apply left-prefix and lexicographic ordering rules to design effective composite indexes.
    • Compare alternative column orders for mixed filtering, joining, and sorting workloads.
    • Reason about nuanced cases where a composite index partly helps but not in the way beginners expect.
  7. 7

    Covering Indexes and Index-Only Scans in PostgreSQL

    • Explain the difference between a logically covering index and a true PostgreSQL index-only scan.
    • Use INCLUDE columns appropriately without confusing them with sort or search keys.
    • Assess when index-only scans are realistic in production given MVCC visibility constraints.
  8. 8

    Partial and Expression Indexes for Real Application Workloads

    • Design partial indexes that target hot subsets of data without overgeneralizing their usefulness.
    • Use expression indexes to restore sargability for repeated computed predicates.
    • Evaluate correctness and maintainability risks introduced by specialized index definitions.
  9. 9

    Beyond B-Trees: When PostgreSQL Needs Other Index Types

    • Identify situations where B-trees are the wrong tool and another PostgreSQL index type is more appropriate.
    • Compare access methods conceptually without losing focus on backend-developer decision making.
    • Recognize how operator classes and supported operations determine whether an index is usable.
  10. 10

    Planner Fundamentals: Cost Models, Estimates, and Plan Search

    • Explain how PostgreSQL chooses among candidate execution plans using cost estimates rather than direct timing measurements.
    • Interpret planner choices in terms of cardinality estimates, startup cost, total cost, and path alternatives.
    • Recognize the limits of cost-based optimization and why seemingly irrational plans often trace back to bad estimates or reasonable assumptions.
  11. 11

    Reading EXPLAIN and EXPLAIN ANALYZE with Confidence

    • Read PostgreSQL execution plans top-down and bottom-up without confusing logical SQL order with physical execution order.
    • Interpret key plan fields and distinguish planner estimates from measured runtime behavior.
    • Use EXPLAIN safely and meaningfully in development and production-adjacent investigations.
  12. 12

    Scan Nodes and Index Access Strategies in Query Plans

    • Differentiate sequential scans, index scans, index-only scans, bitmap index scans, and bitmap heap scans in PostgreSQL plans.
    • Infer why the planner chose a given scan type from row estimates, heap locality, and predicate shape.
    • Diagnose common misunderstandings such as treating any index use as automatically superior.
  13. 13

    Join, Sort, and Aggregate Plan Nodes Through the Lens of Indexing

    • Explain how indexes influence join strategies, sort avoidance, grouping, and top-N queries.
    • Predict when an index can reduce or eliminate expensive sort work.
    • Connect access path choices to nested loop, hash join, and merge join behavior.
  14. 14

    Statistics and Cardinality Estimation in PostgreSQL

    • Explain how PostgreSQL gathers and uses column statistics to estimate result sizes.
    • Diagnose plan mistakes caused by stale statistics, skew, correlation, and independence assumptions.
    • Know when extended statistics can fix estimation problems that indexing alone cannot solve.
  15. 15

    Diagnosing Slow Queries Systematically

    • Follow a repeatable workflow for investigating slow PostgreSQL queries without jumping prematurely to index creation.
    • Use plan evidence, statistics, and workload context to isolate whether the root cause is access path, estimation, SQL shape, or system conditions.
    • Communicate performance findings in a way that supports safe engineering decisions.
  16. 16

    Production Signals and PostgreSQL Tooling for Index Investigation

    • Use PostgreSQL runtime statistics and catalog views to identify high-impact indexing and query-planning issues.
    • Correlate aggregate workload metrics with individual plan analysis.
    • Avoid overreacting to single-query anecdotes by using system-level evidence.
  17. 17

    Index Maintenance, Bloat, and Lifecycle Costs

    • Assess the full lifecycle cost of an index, including storage, write amplification, vacuum interaction, and bloat.
    • Recognize symptoms of unhealthy indexes and choose appropriate maintenance responses.
    • Balance read performance gains against operational overhead in production systems.
  18. 18

    Application Patterns: Pagination, Multi-Tenancy, and API Query Design

    • Map backend API query patterns to appropriate indexing strategies and planner expectations.
    • Choose between alternative query shapes based on how they interact with indexes and result ordering.
    • Anticipate performance issues introduced by generic filtering, ad hoc sorting, and tenant scoping.
  19. 19

    Choosing, Testing, and Justifying Index Changes

    • Make evidence-based indexing decisions that account for benefits, regressions, and maintenance cost.
    • Design meaningful experiments to compare alternative indexes or query rewrites.
    • Document and justify index changes in a way that survives future workload shifts.
  20. 20

    Capstone Synthesis: Reading Plans and Designing Indexes End-to-End

    • Synthesize storage, indexing, statistics, and planner concepts into a coherent performance-debugging method.
    • Evaluate alternative explanations for slow queries and defend the most likely root cause.
    • Demonstrate mature judgment about when to index, rewrite SQL, refresh statistics, or accept the current plan.

Create an account and HippoCampus writes this course for you from the brief above. You can change the brief before it starts.

Already have an account? Log in

HOW IT WORKS
1Sign up, or log in if you already use HippoCampus.
2Your course is written from this brief, chapter by chapter.
3Answer a few cards a day. Each review is scheduled for you.
ALSO ON YOUR PHONE
Download on the App StoreGet it on Google Play