Path 1 · lesson 2 of 8
Indexes and access patterns
When an index is used, what it costs on writes, and why Postgres lets you index anything while Cassandra answers only the queries you designed for.
What you will learn
- Order the columns of a composite index to match the queries it serves
- Predict when the planner will ignore an index, from the statistics it keeps
- Use a covering index to answer a query without reading the table
- Account for the write and storage cost of each index you add
- Explain why Cassandra rejects queries that are not by key, and what ALLOW FILTERING and SAI change
Before you start
The concept
An index is a second copy of some of your data, kept in a different order, so that one kind of question can be answered without reading everything. That sentence holds the whole trade-off. It is a copy, so every write pays to keep it up to date. It is in one order, so it helps the questions that match that order and not the others.
The two engines in this academy take opposite positions on who decides. Postgres lets you create any index you like and leaves the choice of whether to use it to its planner, query by query, using statistics about your data. Cassandra builds one access path into the table itself, the partition key, and refuses by default to run a query that would have to look anywhere else.
| Postgres | Cassandra | |
|---|---|---|
| Main access path | Any index you create, chosen by the planner | The partition key, then clustering columns in order |
| A query no index serves | Runs, as a full table scan | Rejected unless you add ALLOW FILTERING |
| Secondary indexes | B-tree, GIN, GiST, BRIN and more; any number | Storage-attached indexes (SAI, new in 5.0), per column |
| Who decides | The planner, from statistics, every time | You, when you design the table |
Why it exists
Without an index, the only way to find rows is to read all of them. That is fine for a thousand rows and ruinous for a billion. But every index also has to be updated on every insert, update and delete that touches its columns, takes disk and memory, and competes for the cache. So the real skill is not adding indexes: it is choosing the few that serve the questions you actually ask, and knowing when the database will and will not use them.
The engines differ for a reason. A Postgres database runs on one primary, so a query that scans a whole table is slow but bounded. A Cassandra table is spread across many nodes by its partition key, so a query that is not by key may have to ask every node for every partition. Cassandra makes you opt in to that cost explicitly.
How it works inside
Postgres: a composite index and its column order
The lab loads 2,000,000 orders for 20,000 customers into Postgres 18 and asks one question: the orders of customer 4242 in March. Without an index on customer_id, there is only one plan.
Query planNo index on customer_id: a full scan for 9 rows
Real database output Gather (actual rows=9.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=12385 read=4282 written=38
-> Parallel Seq Scan on orders (actual rows=3.00 loops=3)
Filter: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone) AND (customer_id = 4242))
Rows Removed by Filter: 666664
Buffers: shared hit=12385 read=4282 written=38A composite index on (customer_id, created_at) stores entries sorted by customer, then by time within each customer. The query names one customer and a time range, so all its entries sit next to each other in the index.
Query planWith an index on (customer_id, created_at)
Real database output Bitmap Heap Scan on orders (actual rows=9.00 loops=1)
Recheck Cond: ((customer_id = 4242) AND (created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=9
Buffers: shared hit=2 read=10
-> Bitmap Index Scan on orders_customer_created (actual rows=9.00 loops=1)
Index Cond: ((customer_id = 4242) AND (created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone))
Index Searches: 1
Buffers: shared read=3Heap Blocks: exact=9).Column order is the whole design. The Postgres documentation states the rule exactly: equality constraints on the leading columns, plus an inequality on the first column without an equality, limit the part of the index that is scanned. Ask for one hour of orders across all customers, a constraint on the second column only, and this index is no help: the matching entries are scattered across all 20,000 customers. The lab's planner chose a full table scan.
Postgres 18: the skip scan
Postgres 18 can sometimes use an index whose leading column the query does not mention, by skipping: it searches the index once for each distinct value of the leading column. The documentation says this pays off only when that column has "so few distinct values" that most of the index can be skipped. The lab adds an index on (status, created_at), where status has three values, and asks the same one-hour question.
Query planSkip scan: the leading column has three values
Real database outputIndex Scan using orders_status_created on orders (actual rows=240.00 loops=1) Index Cond: ((created_at >= '2026-03-01 10:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-01 11:00:00+00'::timestamp with time zone)) Index Searches: 7 Buffers: shared hit=12 read=18
status at all, yet the index is used. Index Searches: 7 is the scan jumping into the index seven times, around the three status values, instead of reading it end to end: 30 pages instead of 16,667. With 20,000 customers leading the other index, the planner rightly did not try.Selectivity: when the planner ignores a perfectly good index
An index scan reads index pages and then jumps to table pages one by one; a sequential scan reads the table in order. When a query matches a large share of the table, the jumps cost more than reading everything. The planner estimates the share from statistics it gathers with ANALYZE.
Database outputWhat the planner knows about status
Real database output attname | n_distinct | most_common_vals | most_common_freqs
---------+------------+-------------------------+-------------------------------
status | 3 | {paid,pending,refunded} | {0.9001333,0.081,0.018866668}Query planA rare value: refunded orders in January
Real database output -> Index Only Scan using orders_status_created on orders (actual rows=3571.00 loops=1)
Index Cond: ((status = 'refunded'::text) AND (created_at < '2026-02-01 00:00:00+00'::timestamp with time zone))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4 read=18Query planA common value: every paid order
Real database output -> Parallel Seq Scan on orders (actual rows=600000.00 loops=3)
Filter: (status = 'paid'::text)
Rows Removed by Filter: 66667
Buffers: shared hit=13215 read=3452Functions on columns: half true
A common rule says wrapping a column in a function stops an index being used. The lab shows a more precise truth. Asking for March as date_trunc('month', created_at) = '2026-03-01' instead of a range:
Query planA function on the second column
Real database output -> Index Only Scan using orders_customer_created on orders (actual rows=9.00 loops=1)
Index Cond: (customer_id = 4242)
Filter: (date_trunc('month'::text, created_at) = '2026-03-01 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 91
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4customer_id. What changed: created_at moved from the Index Cond to a Filter, so the scan read all 100 of the customer's entries and discarded 91. Cheap here, because one customer has only 100 orders.The cost appears when the function is on the column that does the narrowing. If created_at were the leading column, or a customer had a million orders, the same function would mean reading far more of the index or the table. The fix is to write a range on the bare column (as the lab's next query does, with the time range in the Index Cond again), or to create an index on the expression itself.
Covering indexes: never touch the table
The composite index found 9 entries and then read 9 table pages, because the query also asked for total, which was not in the index. Adding it with INCLUDE makes the index cover the query:
Query planA covering index with INCLUDE (total)
Real database outputIndex Only Scan using orders_customer_created_cov on orders (actual rows=9.00 loops=1) Index Cond: ((customer_id = 4242) AND (created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone)) Heap Fetches: 0 Index Searches: 1 Buffers: shared hit=1 read=3
Heap Fetches: 0. As the documentation explains, this only works when the visibility map says the table pages are all-visible, which is why the lab runs VACUUM first.What every index costs
Database outputIndex sizes, and inserting 100,000 rows with and without them
Real database outputindex | size -----------------------------+------- orders_customer_created | 60 MB orders_customer_created_cov | 77 MB orders_pkey | 43 MB orders_status_created | 61 MB Time: 2756.993 ms (00:02.757) Time: 225.385 ms
Cassandra: the partition key is the access path
In Cassandra the table is designed around a query. The lab's orders_by_customer table has customer_id as its partition key and created_at as its first clustering column, so the orders of one customer are stored together, sorted by time. The question it was designed for is one partition:
Read traceBy partition key and a time range, traced
Real database outputExecuting single-partition query on orders_by_customer Acquiring sstable references Skipped 0/3 non-slice-intersecting sstables, included 0 due to tombstones Partition index found for sstable 1, size = 0 Bloom filter allows skipping sstable 2 Bloom filter allows skipping sstable 3 Merged data from memtables and 1 sstables Read 8 live rows and 0 tombstone cells
Asking a question the table was not designed for, refunded orders across all customers, is refused:
Database outputBy status, which is not part of the primary key
Real database outputInvalidRequest: Error from server: code=2200 [Invalid query] message= Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING
Read traceThe same query with ALLOW FILTERING, traced
Real database outputComputing ranges to query Submitting range requests on 17 ranges with a concurrency of 1 (15408.0 rows per range expected) Submitted 1 concurrent range requests Executing seq scan across 3 sstables for (min(-9223372036854775808), min(-9223372036854775808)] Read 4937 live rows and 0 tombstone cells
Cassandra 5.0 adds storage-attached indexes (SAI). An SAI index is built per column as memtables and SSTables are written, so it lives with the data on each node. With one on status, the query is accepted without ALLOW FILTERING:
Read traceWith an SAI index on status, traced
Real database outputIndex mean cardinalities are orders_status_sai:9223372036854775807. Scanning with orders_status_sai. Computing ranges to query Submitting range requests on 17 ranges with a concurrency of 17 Submitted 1 concurrent range requests Executing read on shop.orders_by_customer using index orders_status_sai Executing single-partition query on orders_by_customer Acquiring sstable references Merging memtable contents Key cache hit for sstable 2, size = 0 Executing single-partition query on orders_by_customer Acquiring sstable references Merging memtable contents Bloom filter allows skipping sstable 2 Key cache hit for sstable 1, size = 0 (50 single-partition reads in this page of results)
Architecture
Drawing diagram…
Flowchart. The same query goes to Postgres and Cassandra. In Postgres the planner reads statistics and uses an index scan of 12 pages when a suitable index exists and few rows match, otherwise a sequential scan of 16,667 pages. In Cassandra, a query with the partition key reads one partition; without it, an SAI index reads every token range with one read per matching partition, and without an index the query is rejected unless ALLOW FILTERING makes it scan everything.
flowchart TB
Q["Query: orders of customer 4242 in March"]
subgraph PG["Postgres"]
P1["Planner reads statistics"] --> P2{"Index on customer_id, created_at?"}
P2 -->|yes, few rows| P3["Index scan: 12 pages"]
P2 -->|no, or most rows match| P4["Sequential scan: 16,667 pages"]
end
subgraph CA["Cassandra"]
C1{"Partition key in the WHERE clause?"}
C1 -->|yes| C2["One partition on one replica set"]
C1 -->|no, SAI index on the column| C3["Every token range, one read per matching partition"]
C1 -->|no index| C4["Rejected, unless ALLOW FILTERING: scan everything"]
end
Q --> P1
Q --> C1- Postgres decides per query. The same table can get an index scan for one value and a sequential scan for another, as the rare and common status values showed.
- Cassandra decides at design time. The partition key is the one cheap path; everything else is either refused or spread across the whole cluster.
- In both, the cost of a query that does not match an access path grows with the size of the data, not with the size of the result.
Step by step
Drawing diagram…
Sequence diagram. The application sends a query for one customer's March orders. The planner estimates the number of matching rows, descends the composite index to the first matching entry, reads nine entries in time order, fetches the nine table pages they point to and returns nine rows. A note says a covering index removes the table step.
sequenceDiagram participant A as Application participant P as Postgres planner participant I as Index orders_customer_created participant T as Table orders A->>P: SELECT id, total WHERE customer_id = 4242 AND created_at in March P->>P: Estimate matching rows from statistics P->>I: Descend to customer 4242, first March entry I-->>P: 9 entries, already in time order P->>T: Fetch the 9 table pages the entries point to T-->>A: 9 rows Note over P,T: With INCLUDE (total) the last step disappears: an index-only scan
- The planner's estimate decides everything after it. A wrong estimate, from stale statistics, can send the same query to the wrong plan; the Postgres path covers that in depth.
- Because the index is ordered by customer and then time, the 9 entries are contiguous: one descent, then a short walk along the leaf pages.
- Each table page fetched is a separate jump. That is why a covering index helps most when a query returns many rows that are scattered across the table.
In a real system
Start from the queries, not the columns. List the access patterns your service runs most, with their filters and sort order, and design indexes (in Postgres) or tables (in Cassandra) for those. Then measure: EXPLAIN (ANALYZE, BUFFERS) in Postgres and TRACING ON in Cassandra tell you what each query actually read.
- Equality columns first, then the range column, in a Postgres composite index. A query that skips the leading column will not use the index unless that column has very few values (Postgres 18's skip scan).
- Do not index low-selectivity columns for their common values. An index on a status column helps find the rare statuses; the planner will rightly ignore it for the common one.
- Every index is paid for on every write. In the lab, three secondary indexes made a bulk insert about twelve times slower. Drop indexes nobody uses:
pg_stat_user_indexes.idx_scanshows how often each one is used. - In Cassandra, a new access pattern usually means a new table, written alongside the first. SAI is for occasional or low-volume queries on other columns, not for the main path of a busy service.
- Treat ALLOW FILTERING in application code as a bug. Cassandra 5.0 can forbid it with the
allow_filtering_enabledguardrail, set incassandra.yamlon every node.
Wrong, then right
An orders dashboard in Postgres needs a customer's orders for a month, with their totals. The first attempt indexes each column the dashboard filters on, separately, and asks for the month with a function.
Incorrect implementation
-- One index per column the dashboard filters on.CREATE INDEX orders_customer_idx ON orders (customer_id);CREATE INDEX orders_status_idx ON orders (status);CREATE INDEX orders_created_idx ON orders (created_at);-- The dashboard query.SELECT created_at, totalFROM ordersWHERE customer_id = $1 AND date_trunc('month', created_at) = $2ORDER BY created_at DESC;Why it is written this way
- Three single-column indexes, each kept up to date on every insert, for one query that can use at most one of them well.
date_trunconcreated_atmeans the index oncreated_atcannot narrow the search: the condition becomes a filter on rows already found bycustomer_id.
Why it fails
- The planner can use
orders_customer_idxto find the customer, then has to read every one of that customer's rows from the table to check the month and fetchtotal. For a customer with years of history, that is thousands of table pages for one month. orders_status_idxwill be ignored for the common statuses (the lab's planner chose a full scan for 90% of rows) and still costs space and write time on every insert.- Every index slows every insert. The lab measured three extra indexes making a bulk insert about twelve times slower.
Corrected implementation
-- One index for the dashboard's access pattern: equality, then range, covering the result.CREATE INDEX orders_customer_created ON orders (customer_id, created_at) INCLUDE (total);-- The same question as a range on the bare column.SELECT created_at, totalFROM ordersWHERE customer_id = $1 AND created_at >= $2 AND created_at < $2 + interval '1 month'ORDER BY created_at DESC;Why it is written this way
- Customer first (equality), then time (range): one descent finds the month, and the entries are already in time order, so
ORDER BY created_at DESCneeds no sort. INCLUDE (total)makes it an index-only scan: the lab's version of this query read 4 pages and no table pages.- A half-open range (
>=start,<next month) is exact for timestamps and keepscreated_atin the index condition.
Trade-offs
- One wider index instead of three narrow ones: the covering index was the largest in the lab (77 MB), because it stores
totaltoo. - It serves this access pattern and its prefixes (any query by
customer_idalone). A query by month across all customers needs a different index, or a different store. - Index-only scans depend on VACUUM keeping the visibility map current; on a table with heavy churn, some table pages will still be read.
Configuration
The settings that shape how each engine picks or permits an access path. Defaults are those of PostgreSQL 18 and Cassandra 5.0, read from a running instance or the shipped configuration.
random_page_cost (Postgres)Default 4
- What it does
- The planner's cost of reading a page out of order, relative to
seq_page_cost(default 1). - When to change it
- On SSDs, or when the data fits in memory, lowering it (to around 1.1 to 2) makes index scans look cheaper and is a common change. Measure plans before and after.
effective_cache_size (Postgres)Default 4GB
- What it does
- The planner's assumption about how much data the OS and Postgres caches can hold. It does not allocate anything.
- When to change it
- Set it to roughly the memory available for caching; too low makes the planner avoid index scans it should use.
default_statistics_target (Postgres)Default 100
- What it does
- How detailed the statistics
ANALYZEgathers per column (most common values, histogram buckets). - When to change it
- Raise it for a column with skewed values whose plans go wrong, with
ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, rather than globally.
allow_filtering_enabled (Cassandra guardrail)Default true
- What it does
- Whether queries with ALLOW FILTERING are permitted at all.
- When to change it
- Set it to false in
cassandra.yamlon every node of a production cluster so that a full-scan query cannot be shipped by accident.
Hands-on exercise
Watch the planner and the partition key decide
Run the lab against real Postgres 18 and Cassandra 5.0 and reproduce each decision: full scan versus composite index, the skip scan, the planner ignoring an index for a common value, the covering index, the write cost, and Cassandra refusing, filtering and indexing a query by status.
Steps · 0 of 5 done
What you should see
A parallel sequential scan of the whole table without an index, against 12 pages with the composite index; a sequential scan for a query on the second column only, but an index scan with several index searches when the leading column has three values; an index-only scan for refunded orders and a sequential scan for paid ones; a covering-index plan with no table pages; a much slower insert with the extra indexes in place. In Cassandra: one partition and one SSTable for the key query, an error for the status query, a full scan that reads far more rows than it returns with ALLOW FILTERING, and one partition read per matching partition with SAI. Your exact numbers will differ.
What can go wrong
A composite index in the wrong order
- Symptom
- An index on (created_at, customer_id) exists, yet the query for one customer's month scans many index pages.
- Cause
- The range column leads, so the equality on customer_id cannot narrow the scan: every entry in the date range, for every customer, is read and checked.
- Fix
- Put equality columns first and the range column last: (customer_id, created_at).
Indexing a column for its common value
- Symptom
- An index on status exists, but queries for status = 'paid' still run as full scans.
- Cause
- The planner's statistics say most rows match, so reading the table in order is cheaper than jumping from the index.
- Fix
- Keep such an index only if the rare values are queried, or make it partial (
WHERE status <> 'paid') so it is small and only covers them.
Index-only scans that keep reading the table
- Symptom
- The plan says Index Only Scan, but Heap Fetches is in the thousands.
- Cause
- The visibility map is not current, so Postgres must visit table pages to check visibility.
- Fix
- Make sure autovacuum keeps up on that table; check
last_autovacuuminpg_stat_user_tables.
ALLOW FILTERING in production code
- Symptom
- A Cassandra query that was fast in development times out as the table grows.
- Cause
- It scans every token range on every node; its cost grows with the table, not with the result.
- Fix
- Add a table designed for the query, or an SAI index if the query is rare. Turn the
allow_filtering_enabledguardrail off to stop it recurring.
How to troubleshoot
A Postgres query is slow and you are not sure it uses the index
Check
EXPLAIN (ANALYZE, BUFFERS) <query>
- How to read it
- Look for the node that reads the table:
Seq Scanread every page;Index ScanorBitmap Heap Scanread the pages the index pointed to. CompareBufferswith the table's page count, andRows Removed by Filterwith the rows returned.
The planner chooses a full scan though an index exists
Check
SELECT n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = '<t>' AND attname = '<col>';
- How to read it
- If the value is common, the full scan is right. If the statistics are stale or wrong, run
ANALYZE <table>and check the plan again.
Writes to a Postgres table got slower over time
Check
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE relname = '<t>';
- How to read it
- Indexes with an
idx_scanof zero since statistics were reset are pure write cost: candidates to drop.
A Cassandra query is slower than a key lookup should be
Check
cqlsh> TRACING ON; then run the query
- How to read it
Executing single-partition queryonce is a key read.Executing seq scanorSubmitting range requestsmeans every token range was asked; with SAI, count the single-partition reads that follow.
Key takeaways
- An index is a copy in one order: it serves the queries that match that order and costs time on every write.
- In a Postgres composite index, equality columns go first and the range column last; a query that skips the leading column needs Postgres 18's skip scan and a leading column with few values.
- The planner ignores an index when most rows match; its statistics, not the existence of the index, decide the plan.
- A covering index (INCLUDE) answers a query from the index alone, given a current visibility map.
- In Cassandra the partition key is the access path; other queries are refused, scanned with ALLOW FILTERING, or served by SAI at the cost of one read per matching partition.
Practical check
Small scenarios, one decision each. Pick what you would do, then read why.
Scenario 1 of 3
A Postgres table of 50 million events has an index on (created_at, account_id). The busiest query is WHERE account_id = $1 AND created_at > now() - interval '1 day'. It reads about 400,000 index entries to return 30 rows.
What is the best change?
Scenario 2 of 3
An orders table has an index on status. 95% of orders are 'delivered'. A report runs SELECT count(*) WHERE status = 'delivered' and the plan is a sequential scan. A teammate wants to force the index with a planner setting.
What do you tell them?
Scenario 3 of 3
A Cassandra table messages_by_channel has PRIMARY KEY ((channel_id), sent_at). Product wants to show all messages flagged for moderation across every channel, a few hundred a day out of tens of millions, on an internal page used by five people.
Which approach fits best?
References
References: PostgreSQL 18 documentation: Multicolumn indexes (leading columns, skip scan) ↗ · PostgreSQL 18 documentation: Index-only scans and covering indexes ↗ · PostgreSQL 18 documentation: Indexes on expressions ↗ · PostgreSQL 18 documentation: Using EXPLAIN ↗ · PostgreSQL 18 documentation: pg_stats view (planner statistics) ↗ · PostgreSQL 18 documentation: Planner cost constants (random_page_cost, effective_cache_size) ↗ · Apache Cassandra 5.0 documentation: Data manipulation, SELECT and ALLOW FILTERING ↗ · Apache Cassandra 5.0 documentation: Storage-attached indexing (SAI) overview ↗ · Apache Cassandra 5.0 documentation: SAI concepts ↗ · Apache Cassandra 5.0: What's new (Storage Attached Indexes) ↗ · Apache Cassandra 5.0 default cassandra.yaml (allow_filtering_enabled guardrail) ↗ · Report a problem