Sorted, Unsorted, and Covering Indexes
Heaps, INCLUDE Columns, and Index-Only Scans
LinkedIn Hook
"Your query has a perfect index on
(user_id), hits exactly one row, and STILL does a disk read for every result. Why?"Because finding the row in the index is only half the work. The index entry points back to the heap — the unsorted pile of table rows — and Postgres has to jump there to fetch the columns you actually asked for. One index lookup, one heap fetch. Multiply that by ten thousand rows and your "indexed" query is doing twenty thousand random I/Os.
The fix is a covering index: an index that carries every column the query needs, so the planner never has to touch the heap at all. In Postgres you spell it with
INCLUDE, in MySQL you bolt extra columns onto the key, and in both cases the magic phrase in EXPLAIN is Index Only Scan. No heap visits. No random reads. Just a tight walk through the index pages.Most engineers learn that "indexes make queries fast." Senior engineers learn that indexes are sorted, tables are not, and the gap between the two is where your query latency lives. Once you understand the heap, the visibility map, and what INCLUDE columns actually do, you stop adding indexes by guesswork and start designing them around the queries that matter.
In Lesson 9.5, I break down sorted vs unsorted storage, covering indexes with INCLUDE, index-only scans, and the exact conditions under which the Postgres planner will (and will not) skip the heap.
Read the full lesson -> [link]
#PostgreSQL #Database #Indexing #SQL #Performance #BackendEngineering #InterviewPrep
What You'll Learn
- The difference between sorted index storage and the unsorted heap that holds the actual rows
- What a covering index is and how Postgres
INCLUDEcolumns differ from indexed key columns - How to read
Index Only ScanvsIndex ScanvsBitmap Heap Scanin EXPLAIN output - Why the visibility map decides whether Postgres can really skip the heap
- How MySQL/InnoDB covering indexes differ from PostgreSQL (clustered vs heap-organized tables)
- The exact conditions the planner checks before choosing an index-only scan
- When adding INCLUDE columns helps and when it just bloats the index for nothing
The Library Card Catalog Analogy — Why the Heap Exists
Imagine an old-school library. The books sit on shelves in the order they arrived, not in alphabetical order — that is the heap. Books were placed wherever there was free space when they showed up. New donations on Monday went on shelf 3, Tuesday's arrivals went on shelf 17, and nobody re-sorts the shelves at night.
To find a book, you walk to the card catalog — a wooden cabinet of index cards sorted alphabetically by title. You flip to "T", find "The Great Gatsby", and read the card. The card gives you a shelf address: "Shelf 17, slot 4". Now you have to walk across the library to shelf 17 to fetch the book itself.
That walk is the heap fetch. The card catalog is sorted (the index), the shelves are unsorted (the heap), and every lookup is two steps: find the address in the catalog, then go fetch the book.
Now imagine a librarian who is sick of walking. She prints expanded cards that include the book's full summary right on the card itself. A patron who only needs the summary never walks to the shelves — the catalog answers the question completely. That is a covering index. The query is satisfied entirely by the index, and the heap is never touched.
+---------------------------------------------------------------+
| HEAP + INDEX (the normal case) |
+---------------------------------------------------------------+
| |
| INDEX on (last_name) HEAP (unsorted table pages) |
| +---------------+ +-------------------------+ |
| | Adams -> tid | --------> | page 17, slot 4 | |
| | Brown -> tid | --------> | page 3, slot 2 | |
| | Clark -> tid | --------> | page 91, slot 1 | |
| | Davis -> tid | --------> | page 12, slot 7 | |
| +---------------+ +-------------------------+ |
| (sorted) (insertion order) |
| |
| Lookup = 1 index walk + 1 random heap fetch per row |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| COVERING INDEX (the fast case) |
+---------------------------------------------------------------+
| |
| INDEX on (last_name) INCLUDE (first_name, email) |
| +-------------------------------------------+ |
| | Adams | Alice | alice@a.com -> tid | |
| | Brown | Bob | bob@b.com -> tid | |
| | Clark | Carol | carol@c.com -> tid | |
| | Davis | Dan | dan@d.com -> tid | |
| +-------------------------------------------+ |
| |
| Lookup = 1 index walk, ZERO heap fetches |
| EXPLAIN says: "Index Only Scan" |
| |
+---------------------------------------------------------------+Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Two horizontal panels. TOP panel labeled 'Heap + Index': sky-blue (#4fc3f7) sorted index cards on the left with arrows curving to a scattered pink (#ff5c8a) grid of heap pages on the right. BOTTOM panel labeled 'Covering Index': a single sky-blue stack of fat index cards holding all needed columns inline, with a green checkmark labeled 'Index Only Scan' and NO arrows pointing to the heap. White monospace labels throughout. Title: 'Index Storage vs Heap Storage'."
Sorted vs Unsorted — Where Postgres Actually Stores Your Data
In PostgreSQL, every regular table is a heap. The word "heap" is literal: rows are added wherever there is free space inside the table's data files (relfilenode). If you insert one million rows, Postgres does not sort them — it appends them to whichever 8 KB page has room. Update a row, and the new version may end up on a different page entirely. The result is that the physical order of rows on disk has no relationship to any column value.
A B-tree index, on the other hand, is sorted. The leaf pages of the index store entries in key order, with each entry holding the key value plus a tid (tuple identifier — a (page_number, slot_number) pointer back into the heap). When you query WHERE last_name = 'Adams', Postgres descends the B-tree in O(log n), finds the leaf entry, reads the tid, and then jumps to the heap page to fetch the row.
That second jump is the cost most beginners forget. An index lookup is fast, but a heap fetch is a random I/O — and if the query returns many rows scattered across many heap pages, those random I/Os dominate the runtime. This is why Postgres sometimes ignores a "perfect" index for a high-selectivity query: a sequential scan of the heap is faster than thousands of random page fetches.
+---------------------------------------------------------------+
| HOW POSTGRES STORES DATA |
+---------------------------------------------------------------+
| |
| TABLE = HEAP |
| - 8 KB pages, rows appended wherever there is free space |
| - No physical sort order |
| - UPDATE may relocate the row to a new page |
| - Each row has a tid = (page, slot) |
| |
| B-TREE INDEX |
| - Sorted leaf pages, each entry = (key, tid) |
| - O(log n) lookup |
| - Range scans walk leaves left-to-right in key order |
| |
| THE LATENCY GAP |
| - Find tid in index = sequential, cache-friendly |
| - Fetch row from heap = random I/O, possibly cold |
| - Covering index closes the gap by avoiding the heap |
| |
+---------------------------------------------------------------+MySQL/InnoDB note: InnoDB tables are not heaps. They are clustered indexes organized by the primary key — the leaf pages of the primary key index are the table rows. Secondary indexes in InnoDB store the primary key value (not a tid) as the row pointer. This means an InnoDB secondary index lookup is "find PK in secondary index" + "find row in clustered index", and a covering index there means the secondary index already contains every column the query needs without consulting the clustered index. Postgres covering indexes are conceptually identical, but they save a tid -> heap page jump rather than a secondary -> clustered walk.
Covering Indexes and the INCLUDE Clause
A covering index is any index that contains all the columns a query needs to read. If the planner can answer the query entirely from the index without touching the table, that is a covering scan. In PostgreSQL 11+ you have two ways to build one:
- Put every column the query needs into the key of the index:
CREATE INDEX ... ON t (a, b, c). This works, but it makesbandcpart of the sort key — they take space in every internal B-tree node, they participate in uniqueness checks (if any), and they affect how the index walks during range scans. - Use the INCLUDE clause:
CREATE INDEX ... ON t (a) INCLUDE (b, c). Hereais the only key, andb, care stored only in the leaf pages as payload. They are not part of the sort order and they are not in the upper B-tree levels — they exist purely so the leaf entry already contains the data the query wants.
The INCLUDE form is almost always preferable when the extra columns are not used for filtering or ordering. It keeps the index narrow at the upper levels (faster descents, smaller cache footprint), avoids meaningless extra entries in the sort order, and is the only correct way to add payload columns to a UNIQUE index without breaking uniqueness semantics.
Example 1 — Build a Covering Index and Watch EXPLAIN Switch to Index Only Scan
-- Create a small users table, populate it, and observe the plans
-- before and after we add a covering index. All comments in English.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
last_name TEXT NOT NULL,
first_name TEXT NOT NULL,
email TEXT NOT NULL,
signup_date DATE NOT NULL,
country TEXT NOT NULL
);
-- Insert 200,000 synthetic rows so the planner has real statistics
INSERT INTO users (last_name, first_name, email, signup_date, country)
SELECT
'Last' || (g % 5000),
'First' || (g % 5000),
'user' || g || '@example.com',
DATE '2020-01-01' + (g % 1500),
(ARRAY['US','UK','DE','FR','JP'])[1 + (g % 5)]
FROM generate_series(1, 200000) AS g;
-- Plain B-tree index on the lookup column only
CREATE INDEX idx_users_last_name ON users (last_name);
-- Make sure stats and the visibility map are fresh
ANALYZE users;
VACUUM users;
-- Query that needs first_name and email in addition to the lookup key
EXPLAIN (ANALYZE, BUFFERS)
SELECT first_name, email
FROM users
WHERE last_name = 'Last42';Sample output (plain index, no INCLUDE):
Bitmap Heap Scan on users (cost=4.55..151.20 rows=40 width=27)
(actual time=0.082..0.341 rows=40 loops=1)
Recheck Cond: (last_name = 'Last42'::text)
Heap Blocks: exact=40
Buffers: shared hit=43
-> Bitmap Index Scan on idx_users_last_name
(cost=0.00..4.54 rows=40 width=0)
(actual time=0.041..0.041 rows=40 loops=1)
Index Cond: (last_name = 'Last42'::text)
Buffers: shared hit=3
Planning Time: 0.118 ms
Execution Time: 0.402 msNotice "Bitmap Heap Scan" and Heap Blocks: exact=40. Postgres found 40 matching tids in the index, then did 40 heap fetches because first_name and email are not in the index. Now drop the old index and rebuild it as a covering index.
DROP INDEX idx_users_last_name;
-- last_name is the only key; first_name and email ride along as payload
CREATE INDEX idx_users_last_name_covering
ON users (last_name)
INCLUDE (first_name, email);
VACUUM ANALYZE users;
EXPLAIN (ANALYZE, BUFFERS)
SELECT first_name, email
FROM users
WHERE last_name = 'Last42';Sample output (covering index):
Index Only Scan using idx_users_last_name_covering on users
(cost=0.42..8.74 rows=40 width=27)
(actual time=0.028..0.061 rows=40 loops=1)
Index Cond: (last_name = 'Last42'::text)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.092 ms
Execution Time: 0.084 msThe plan node is now Index Only Scan, Heap Fetches: 0, and the buffer hits dropped from 43 to 4. The query went from ~0.4 ms to ~0.08 ms — a 5x improvement on warm cache, and the gap is much larger on cold cache where every avoided random I/O is a real disk seek.
Example 2 — Why "Heap Fetches" Is Sometimes Greater Than Zero
An index-only scan in Postgres has a subtle requirement: the planner can only skip the heap if it knows that the row is visible to the current transaction without checking MVCC tuple headers. That information lives in the visibility map (VM) — one bit per heap page that says "all tuples on this page are visible to all transactions." If the bit is set, the scan can return data straight from the index. If the bit is not set, Postgres must visit the heap page to check tuple visibility — defeating the purpose of the covering index.
The visibility map is updated by VACUUM. After heavy writes, recently modified pages have their VM bit cleared until the next vacuum. This is why benchmarks of covering indexes always run VACUUM first, and why a covering index on a write-heavy table can show Heap Fetches: 5000 instead of Heap Fetches: 0 in production.
-- Force the situation: insert fresh rows, do NOT vacuum, and watch
-- Heap Fetches climb above zero even though the index covers the query.
INSERT INTO users (last_name, first_name, email, signup_date, country)
SELECT
'Last42',
'Fresh' || g,
'fresh' || g || '@example.com',
CURRENT_DATE,
'US'
FROM generate_series(1, 100) AS g;
-- Skip VACUUM intentionally
EXPLAIN (ANALYZE, BUFFERS)
SELECT first_name, email
FROM users
WHERE last_name = 'Last42';Sample output (post-insert, pre-vacuum):
Index Only Scan using idx_users_last_name_covering on users
(cost=0.42..15.20 rows=140 width=27)
(actual time=0.034..0.298 rows=140 loops=1)
Index Cond: (last_name = 'Last42'::text)
Heap Fetches: 100
Buffers: shared hit=27
Planning Time: 0.098 ms
Execution Time: 0.371 msThe plan is still Index Only Scan, but Heap Fetches: 100 shows that 100 of the 140 rows triggered a visibility check against the heap. After VACUUM users, those VM bits get set and Heap Fetches drops back to 0 on the next run. A covering index without a healthy autovacuum schedule is a covering index in name only.
Example 3 — Covering Indexes for ORDER BY + LIMIT Pagination
Covering indexes shine for keyset pagination, where you walk an index in order and stop after LIMIT n. Here the index sort order matches the query's ORDER BY, and the INCLUDE columns supply the projection — Postgres walks the leaves left to right and never touches the heap.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
total_cents INT NOT NULL,
status TEXT NOT NULL
);
INSERT INTO orders (customer_id, created_at, total_cents, status)
SELECT
1 + (g % 10000),
NOW() - (g || ' minutes')::interval,
100 + (g % 50000),
(ARRAY['paid','pending','refunded'])[1 + (g % 3)]
FROM generate_series(1, 500000) AS g;
-- Covering index: sort key is created_at DESC for fast pagination,
-- and the INCLUDE columns carry exactly what the dashboard renders.
CREATE INDEX idx_orders_recent
ON orders (created_at DESC)
INCLUDE (customer_id, total_cents, status);
VACUUM ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, customer_id, total_cents, status
FROM orders
ORDER BY created_at DESC
LIMIT 20;Sample output:
Limit (cost=0.42..1.34 rows=20 width=24)
(actual time=0.029..0.041 rows=20 loops=1)
Buffers: shared hit=4
-> Index Only Scan using idx_orders_recent on orders
(cost=0.42..22871.42 rows=500000 width=24)
(actual time=0.028..0.038 rows=20 loops=1)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.084 ms
Execution Time: 0.058 msHeap Fetches: 0, only 4 buffer hits, and the LIMIT 20 short-circuits the scan after reading exactly the leaf entries it needs. This is the gold-standard plan for "show the latest 20 orders" — and it works the same way for keyset pagination using WHERE created_at < $cursor ORDER BY created_at DESC LIMIT 20.
When the Planner Picks an Index Only Scan
The Postgres planner will consider an index-only scan only when every column referenced by the query (in SELECT, WHERE, ORDER BY, GROUP BY, etc.) is available from the index — either as a key column or as an INCLUDE column. Even a single extra column forces a regular Index Scan or a Bitmap Heap Scan with heap fetches.
+---------------------------------------------------------------+
| INDEX ONLY SCAN — THE CHECKLIST |
+---------------------------------------------------------------+
| |
| 1. Every column the query touches is in the index |
| (key columns OR INCLUDE columns) |
| |
| 2. The visibility map bit is set for the heap pages |
| (means: VACUUM has run since the last write to that page) |
| |
| 3. The cost estimator believes index-only is cheaper than: |
| - Bitmap Heap Scan |
| - Sequential Scan |
| (driven by selectivity statistics from ANALYZE) |
| |
| 4. The index access method supports it (B-tree, GiST, SP-GiST)|
| - GIN does NOT support index-only scans |
| - Hash does NOT support index-only scans |
| |
| Miss any one of these and you get an Index Scan or |
| Bitmap Heap Scan with heap visits. |
| |
+---------------------------------------------------------------+The fourth point is a common gotcha: a GIN index on tsvector cannot drive an index-only scan, no matter how you build it. If you need full-text search results without heap visits, you typically denormalize a tsvector summary into a B-tree-friendly structure or accept the heap fetch.
PostgreSQL vs MySQL — Two Roads to the Same Destination
| Aspect | PostgreSQL | MySQL / InnoDB |
|---|---|---|
| Table storage | Heap (unordered) | Clustered by PK (ordered) |
| Secondary index row pointer | tid (page, slot) | Primary key value |
| Covering syntax | INCLUDE (cols) (PG 11+) | Add columns to the key list |
| Index-only EXPLAIN signal | Index Only Scan | Using index in EXPLAIN |
| Visibility check | Visibility map bit | Not needed (PK is in the row) |
| Risk of heap fetches even with covering index | Yes, if VM bit unset | No equivalent issue |
In MySQL, a covering index is purely a function of which columns you stuck in the key. In Postgres, you should reach for INCLUDE so the extra columns do not pollute the sort order, and you should keep autovacuum healthy so the visibility map keeps Heap Fetches at zero. Both engines reward covering indexes with index-only scans — they just call it different things in EXPLAIN.
Common Mistakes
1. Adding payload columns to the index key instead of INCLUDE.
Engineers new to Postgres covering indexes often write CREATE INDEX ... ON users (last_name, first_name, email) when they really mean "lookup by last_name, also return first_name and email". The first form pollutes the sort key, fattens the upper B-tree levels, and breaks any chance of a UNIQUE (last_name) constraint. The right form is CREATE INDEX ... ON users (last_name) INCLUDE (first_name, email) — same covering benefit, no side effects on the key.
2. Forgetting that SELECT * defeats covering indexes.
A covering index can only cover the columns you actually project. If your query does SELECT * and your table has 30 columns, no sane covering index can hold all 30 — and the planner falls back to a regular Index Scan with heap fetches. Always project only the columns you need, especially for hot paths where you want an Index Only Scan.
3. Ignoring the visibility map after heavy writes.
You build a beautiful covering index, run EXPLAIN, see Index Only Scan, ship it — and then production shows Heap Fetches: 50000 because autovacuum is throttled and the VM bits never get set. Tune autovacuum_vacuum_scale_factor for write-heavy tables, or run an explicit VACUUM after bulk loads. An index-only scan that visits the heap on every call is a regular index scan with extra steps.
4. Adding INCLUDE columns "just in case". Every INCLUDE column makes the leaf pages bigger, which means fewer index entries per page, which means more pages to read for the same range scan. Only INCLUDE columns that are actually projected by the query you are tuning. If three different queries hit the same key but project different columns, build separate covering indexes — or accept that one of them will fall back to a heap fetch.
5. Confusing covering indexes with multi-column indexes.
A multi-column index (a, b, c) and a covering index (a) INCLUDE (b, c) look similar but behave differently. The multi-column form lets you filter on a, on (a, b), or on (a, b, c) — b and c are part of the sort. The INCLUDE form only lets you filter on a; b and c are dead weight for filtering and only useful for projection. Pick the form that matches how you actually query the table.
Interview Questions
1. "What is the difference between a heap and a clustered index, and which one does PostgreSQL use?"
PostgreSQL stores every regular table as a heap — an unordered collection of 8 KB pages where rows are appended wherever there is free space. The physical position of a row has no relationship to any column value, and updates can move rows to entirely new pages. Indexes in Postgres are separate data structures (B-tree, GiST, etc.) whose entries point back to the heap via a tid (page number + slot number). MySQL's InnoDB does the opposite: tables are clustered by the primary key, meaning the leaf pages of the primary-key B-tree literally are the table rows in PK order. Secondary indexes in InnoDB store the primary key as the row pointer rather than a physical address. The practical consequence is that in Postgres every secondary lookup is index page -> heap page (a random I/O), while in InnoDB every secondary lookup is secondary index -> primary key index -> row (two B-tree walks). Covering indexes are valuable in both engines for the same reason: they let the query be answered without doing the second jump.
2. "Explain what a covering index is and how Postgres INCLUDE differs from putting columns in the index key."
A covering index is any index that contains all the columns a query needs to read, allowing the planner to satisfy the query entirely from the index without touching the table. In Postgres 11+, you build one using CREATE INDEX ... ON t (a) INCLUDE (b, c). The key column a participates in the sort order and the upper B-tree levels, while b and c are stored only in the leaf pages as payload — they are not part of the sort key, they do not bloat internal nodes, and they do not interact with UNIQUE constraints on the key. Putting b and c directly in the key (CREATE INDEX ... ON t (a, b, c)) also produces a covering index for queries projecting those columns, but it makes them part of the sort, fattens every level of the B-tree, and means a UNIQUE index would enforce uniqueness on the entire (a, b, c) tuple rather than just a. The INCLUDE form is the right tool when the extra columns are pure projection payload and the key form is the right tool when the extra columns are also used for filtering or ordering.
3. "You see Index Only Scan in EXPLAIN but Heap Fetches: 5000. What is happening and how do you fix it?"
An Index Only Scan in Postgres is allowed to skip the heap only when the visibility map bit is set for the heap page that holds the row. That bit means "every tuple on this page is visible to every active transaction" and it is updated by VACUUM. When rows are inserted, updated, or deleted, the VM bits for the affected pages are cleared. Until the next vacuum runs, an index-only scan that touches those rows must still visit the heap to check tuple visibility — and EXPLAIN ANALYZE reports those visits as Heap Fetches. Seeing Heap Fetches: 5000 means the planner picked an index-only plan, but autovacuum has not caught up with recent writes. The fix is to make autovacuum more aggressive on that table (lower autovacuum_vacuum_scale_factor, lower autovacuum_vacuum_insert_scale_factor on PG 13+), run an explicit VACUUM after bulk loads, and verify with pg_stat_user_tables.last_autovacuum. The covering index itself is fine — the visibility map is the bottleneck.
4. "When does the PostgreSQL planner choose a Sequential Scan over a perfectly good covering index?"
When the index does not actually save work. The planner estimates the cost of every viable plan and picks the cheapest. For a query that returns a large fraction of the table — say 30% or more — walking the index, doing visibility checks, and then walking the leaves can cost more than just streaming every page of the heap sequentially. Sequential I/O is much cheaper per page than random I/O, and a sequential scan benefits from prefetching and tight loops over contiguous memory. The planner uses statistics gathered by ANALYZE (most-common-values, histogram, distinct count) to estimate the result selectivity, and if selectivity is poor it correctly prefers Seq Scan. The fix is rarely to force the index — set enable_seqscan = off is a debugging tool, not a production setting. The fix is usually to make the query more selective, partition the table, or accept that for low-selectivity queries the sequential scan really is the right plan.
5. "How would you design indexes for a 'show me the latest 20 orders for customer X' query that needs to return id, total, and status?"
I would build a single covering index keyed on (customer_id, created_at DESC) with INCLUDE (id, total_cents, status). The key columns let Postgres descend the B-tree to the customer's rows in O(log n) and walk the leaf pages in created_at DESC order, which matches the ORDER BY. The INCLUDE columns carry the projection so the scan never touches the heap. With LIMIT 20, Postgres reads exactly the 20 leaf entries it needs and stops — typically 1 to 4 buffer hits regardless of how many total orders the customer has. The EXPLAIN plan should show Index Only Scan with Heap Fetches: 0 after a vacuum. I would also verify that autovacuum is healthy on the orders table, because a heavy write load could clear visibility bits and degrade the plan to Index Scan with heap fetches. If a second query needs different projection columns, I would build a second covering index rather than fattening this one with columns the original query does not need.
Quick Reference — Cheat Sheet
+---------------------------------------------------------------+
| SORTED, UNSORTED, AND COVERING INDEXES |
+---------------------------------------------------------------+
| |
| TABLE STORAGE |
| - Postgres = HEAP (unordered, tid = page+slot) |
| - MySQL InnoDB = CLUSTERED by primary key |
| |
| INDEX STORAGE |
| - B-tree leaves are sorted by key |
| - Each entry: (key value [, include columns], row pointer) |
| |
| COVERING INDEX SYNTAX |
| CREATE INDEX idx ON t (a) INCLUDE (b, c); |
| - a is the sort key |
| - b, c are leaf-only payload |
| |
| EXPLAIN SIGNALS |
| - 'Index Only Scan' -> heap maybe skipped |
| - 'Heap Fetches: 0' -> heap actually skipped (good) |
| - 'Bitmap Heap Scan' -> index found rows, heap was visited |
| - 'Index Scan' -> one heap visit per matched row |
| |
| VISIBILITY MAP |
| - One bit per heap page |
| - Set by VACUUM, cleared by writes |
| - Required for true zero-heap-fetch index-only scans |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| DECISION TABLE |
+---------------------------------------------------------------+
| |
| Goal: "lookup by X, return X" |
| -> CREATE INDEX ... ON t (X); |
| |
| Goal: "lookup by X, return X, Y, Z" |
| -> CREATE INDEX ... ON t (X) INCLUDE (Y, Z); |
| |
| Goal: "lookup by (X, Y), return X, Y, Z" |
| -> CREATE INDEX ... ON t (X, Y) INCLUDE (Z); |
| |
| Goal: "ORDER BY X DESC LIMIT N, return X, Y" |
| -> CREATE INDEX ... ON t (X DESC) INCLUDE (Y); |
| |
| Goal: "full-text search on a tsvector" |
| -> GIN index, NO index-only scan possible |
| |
+---------------------------------------------------------------+| Concern | Wrong Way | Right Way |
|---|---|---|
| Add payload columns | Tack onto key list | INCLUDE (cols) |
| Hot-path projection | SELECT * | Project only needed columns |
| Bulk load + index | Skip vacuum | VACUUM ANALYZE after load |
| GIN covering scan | Expect Index Only Scan | Accept heap fetch |
| Autovacuum on write-heavy table | Defaults | Lower scale factors |
| Multi-query coverage | One fat index | Separate narrow covering indexes |
UNIQUE + payload | UNIQUE (a, b, c) | UNIQUE (a) INCLUDE (b, c) |
Prev: Lesson 9.4 -- GIN, GiST, Hash, and BRIN Indexes Next: Lesson 9.6 -- Index Best Practices and EXPLAIN
This is Lesson 9.5 of the Database Interview Prep Course -- 12 chapters, 58 lessons.