Skip to content

Path 1 · lesson 2 of 8

Foundation30 minPostgreSQL 18.7 · Apache Cassandra 5.0.9 (both run in Docker for this lesson)

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.

PostgresCassandra
Main access pathAny index you create, chosen by the plannerThe partition key, then clustering columns in order
A query no index servesRuns, as a full table scanRejected unless you add ALLOW FILTERING
Secondary indexesB-tree, GIN, GiST, BRIN and more; any numberStorage-attached indexes (SAI, new in 5.0), per column
Who decidesThe planner, from statistics, every timeYou, when you design the table
Opposite answers to the same question: who decides how a query finds its rows.

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=38
Three processes (two workers and the leader) read all 16,667 pages of the table between them and threw away about 666,664 rows each, to return 9.

A 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=3
12 pages instead of 16,667: 3 index pages to find the 9 entries, then the 9 table pages that hold them (Heap 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 output
 Index 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
No condition on 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}
Three values, with paid in about 90% of rows and refunded in under 2%. These are estimates from a sample, which is why they are not exact.

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=18
22 pages to count 3,571 rows, without touching the table.

Query 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=3452
The same index exists, and the planner ignores it: 90% of the rows match, so reading the whole table in order is cheaper. An index on a column with a few values helps only the rare ones.

Functions 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=4
The index is still used, for customer_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 output
 Index 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
4 pages, and 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 output
            index            | 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
Three secondary indexes take 198 MB between them. Inserting 100,000 rows took 2.76 s with them and 0.23 s after dropping them: about twelve times slower, in this one run, because every row had to be placed in four B-trees instead of one.

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 output
Executing 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
One partition on one node. The Bloom filters of two SSTables said the partition is definitely not in them, so only one file was read.

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 output
InvalidRequest: 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
Postgres would have run this as a full scan. Cassandra makes you say that you mean it.

Read traceThe same query with ALLOW FILTERING, traced

Real database output
Computing 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
A sequential scan of every token range: Cassandra read 4,937 rows to fill one 100-row page of results, because only 1 in 50 orders is refunded. On a real cluster each range is on different nodes.

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 output
Index 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)
The index finds the matches without scanning, but each matching partition is then read separately: 50 single-partition reads for one page of 100 rows. It still asks every token range, now in parallel. Better than ALLOW FILTERING, and still not the same as a table designed for the question.

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
How each engine decides how to find the rows for a query.
  • 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
One indexed query in Postgres, step by step.
  1. 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.
  2. 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.
  3. 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_scan shows 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_enabled guardrail, set in cassandra.yaml on 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

migrations/V12__dashboard_indexes.sqlSQL
-- 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_trunc on created_at means the index on created_at cannot narrow the search: the condition becomes a filter on rows already found by customer_id.

Why it fails

  • The planner can use orders_customer_idx to find the customer, then has to read every one of that customer's rows from the table to check the month and fetch total. For a customer with years of history, that is thousands of table pages for one month.
  • orders_status_idx will 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

migrations/V12__dashboard_indexes.sqlSQL
-- 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 DESC needs 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 keeps created_at in 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 total too.
  • It serves this access pattern and its prefixes (any query by customer_id alone). 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 ANALYZE gathers 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.yaml on 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 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_autovacuum in pg_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_enabled guardrail 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 Scan read every page; Index Scan or Bitmap Heap Scan read the pages the index pointed to. Compare Buffers with the table's page count, and Rows Removed by Filter with 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_scan of 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 query once is a key read. Executing seq scan or Submitting range requests means 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