Clustered versus non-clustered: the only lever on pages touched

Why do you get only one clustered index?

Recall the crossover from Why indexes exist: the index wins when k/N < s/r, where k counts pages, not rows. Clustering is the only mechanism that reduces pages without reducing rows.

Let f be rows per page. A range query returning k rows costs:

So clustering buys up to an f-fold reduction in I/O, and f is typically 20 to 200 for a row store on 8 KB pages.

Substitute this into the crossover and the "5% rule" dissolves into two rules:

• An unclustered index stops helping at low single-digit selectivity (here 0.25% to 1%).
• A clustered index on a range predicate essentially never loses to a scan, because it is the scan, started in the right place.

This is the highest-leverage number in this article. Almost every "why isn't my index being used" question is this arithmetic.

There is only one clustered order per table, because the clustered index is the layout. Indexing dropped the single-order limitation for secondary access and relocated it onto one privileged index. The constraint was not eliminated; it was moved somewhere it hurts less.

Heap-organized versus index-organized tables is a genuine engineering choice, not a settled question. For this mechanism we only need its consequence for index references (physical or logical pointer). In outline:

In PostgreSQL, CLUSTER is a one-time physical reorder that decays with every later write. The planner's correlation statistic is literally a measurement of how far it has decayed.

In column stores the same mechanism returns under other names (sort keys, Z-order, liquid clustering), and the skipping is done by zone maps rather than a tree (sparse indexes and zone maps). Same constraint, same scarcity, different organ.