Database Indexes: Single-Key, Compound, and Covering Indexes

Interactive simulator modeling Full Table Scans vs Single-Key, Compound, and Covering Indexes (Index-Only Scan with 0 heap fetches), plus the column order trap.

Architecture Lab // Database Engine Performance

The Cookbook Index: Full Table Scan vs. Single, Compound & Covering Indexes

Query Time:0.2ms (⚡ Instant)

Rows Scanned:1 row

Disk Heap Fetches:0 pages (Index-Only!)

Strategy:INDEX-ONLY SCAN

lightbulb

Real-World Analogy: The 800-Page Cookbook

Imagine searching for "Chocolate Lava Cake" in an 800-page cookbook:
• No Index (Full Table Scan): You flip page-by-page from page 1 to 800. Exhausting and slow!
• Single Index: The back index lists Cake → Page 312. You jump there in 1 second, but still have to read the heavy page to find the oven temperature (Heap Fetch).
• Compound Index (Category, Dish): Sorted by [Desserts > Cakes > Lava Cake]. Instant jump! But if you search only by cooking time without category, the alphabetical list is useless (Leading Column Trap).
• Covering Index: The index card itself prints the oven temp right next to the title! You get your answer without ever opening the heavy cookbook (Index-Only Scan, 0 Heap Fetches!).

account_tree

Track 1: B-Tree Index & Planner

In-Memory Traversal // RAM Buffer Cache

COVERING LEAF

Incoming SQL Query

Database Index Setup

RAM Index Buffer TreeShared Buffers Hit

alt_routeRoot Page:

user_id: [1..500k] | [500k..1M]

south

subdirectory_arrow_rightBranch Page:

user_id: [1..25000] → Leaf Block 42

south

folder_dataLeaf Node (Order #42):

Key + Included Columns

Stores: (user_id=42, status='COMPLETED') + Payload: total_amount=$189.50

check_circleQuery answered 100% inside B-Tree memory! Heap fetch bypassed.

EXPLAIN (ANALYZE, BUFFERS)

Execution: 0.204 ms

Index Only Scan using idx_covering on orders  (cost=0.42..4.44 rows=1 width=24) (actual time=0.082..0.084 rows=1 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'COMPLETED'::text))
  Heap Fetches: 0
  Buffers: shared hit=3
Planning Time: 0.112 ms
Execution Time: 0.204 ms

hard_drive

Track 2: Physical Table Heap

8KB Page Blocks // Disk I/O Subsystem

0 DISK READS

Table Volume

Buffer Cache Hit:100% (RAM Hit)

Pages Scanned:0 pages (0 KB)

8KB Disk Block Matrix (Sample 32 of 18,400 Pages)Bypassed (0 Fetches)

P01

P02

P03

P04

P05

P06

P07

P08

P09

P10

P11

P12

P13

P14

P15

P16

P17

P18

P19

P20

P21

P22

P23

P24

P25

P26

P27

P28

P29

P30

P31

P32

0 Reads (Index-Only)

Target Heap Fetch (TID)

Seq Scan Disk Thrash

Physical Disk I/O Pressure:0% (Bypassed)

🚀 Zero Disk Reads: Because the covering index has all columns requested, the database never had to spin up disk I/O to read the raw table rows.

verifiedWhat's Happening: Golden Index-Only Scan

By storing total_amount in the leaf node via INCLUDE, the database answered your query entirely inside memory without reading a single page from disk! Heap Fetches: 0. Latency: 0.2ms!

build_circlePRODUCTION REMEDIATION PLAYBOOK

Potential Fixes for Full Table Scans & Slow Queries

1. Covering Index (INCLUDE Clause)

Append high-frequency read columns to index leaf nodes. Turns slow table heap scans into pure Index-Only Scans with 0 disk fetches.

2. The Equality-First Ordering Rule

Always place exact equality filters (tenant_id, status) before range or ordering predicates (created_at) to prevent b-tree branch abandonments.

3. Partial Indexes on Hot Working Sets

Don't index millions of historical rows. Add WHERE status IN ('PENDING', 'PROCESSING') to keep the active index in fast RAM cache.

PostgreSQL DDL // Production Covering Index & Partial Queue Index

-- 1. High-throughput covering index for instant user dashboard queries
-- Eliminates heap page lookups completely via the INCLUDE clause (Index-Only Scan)
CREATE INDEX CONCURRENTLY idx_orders_user_status_covering 
ON orders (user_id, status) 
INCLUDE (total_amount, created_at);

-- 2. Partial index targeting only unfulfilled queue orders (hot 2% working set)
-- Slashes index memory consumption by ~95% and avoids write bloat on historical data
CREATE INDEX CONCURRENTLY idx_orders_unfulfilled_queue 
ON orders (created_at ASC) 
WHERE status IN ('PENDING', 'PROCESSING');

Deep-Dive Technical Reading

All Articlesarrow_forward

Explore the engineering theory, mathematical foundations, and production Go implementations behind this simulator.

Database Performance10 min read

The Hidden Cost of Overfetching — Why SELECT * Is Killing Your Database

Covering indexes, buffer pool memory churn, and zero-heap index-only scans in production.

Read Full Guidearrow_forward

arrow_back

Previous LabTransactional Outbox Pattern

Next LabBig-O & Hardware Limits

arrow_forward

Experiencing architectural bottlenecks in production?

From connection starvation and p99 latency spikes to Kafka consumer lag, index optimization, and algorithm profiling.

handshakeDiscuss Your Architecture