Build1 distinct publisher3 min readPublished
PostgreSQL has done index-only scans since 9.2, six major releases before INCLUDE shipped, so covering was never the thing INCLUDE fixed. What it changes is indnkeyatts, the count of columns the index treats as key.
The Engineer · Build desk
Compiled by The EngineerSomething wrong?How this is made
The delta between the two indexes is one integer in a catalog table. pg_index counts two attributes in each, and one key attribute in the INCLUDE version [7]. Key attributes are the columns the tree sorts and descends on: demo_idx1 orders on (value, id, TID), demo_idx2 on (value, TID), with id carried in the leaf as payload and absent from pivot tuples and every level above the leaf [6].
That absence is the whole mechanism. Both indexes keep value and id in their leaf tuples [6], so both project id without touching the heap, and the two plans come out matching to the digit: the same estimated cost of 0.55..566.29, the same 8785-row estimate, the same 50 shared buffer hits [2][4]. Run the size figure down to the row and it is 95,420,416 bytes over a million rows, about 95 bytes an entry, the same 95 bytes in both objects [3].
Which makes the write-up's account of the planner's choice hard to check. Franck Pachot writes that the planner picked demo_idx2 because its estimated cost is slightly lower [9]. EXPLAIN prints two decimals, and both plans print the same ones [2][4], so whatever separates the candidates lives below the precision on offer. The wall clock went the other way: 0.710 ms for the plain index against 1.323 ms for the INCLUDE one, roughly 1.9x [1], on single runs with identical buffer counts, which reads as timing noise rather than structure. (Both plans also estimated 8785 rows where 4000 came back, 2.2x out [2]; the row estimate is wrong by more than anything separating the two indexes.)
For the pivot-tuple saving to appear in your own numbers, the demoted column has to occupy space you care about above the leaf. That needs narrow keys, a wide payload, and enough depth for the upper levels to weigh anything. Here both columns are md5 text of the same width, 33 bytes as EXPLAIN reports id [2], so there was nothing to reclaim, and the size query says as much.
What is left is semantics, and that is the reason to reach for the clause. indnkeyatts declares how many columns the index treats as key columns [7], and the key columns are what ordering and navigation are built from [6]. If you want a column readable from the leaf and kept out of the key, that integer is the knob you are turning. The material supplied demonstrates the ordering half with pg_index and pageinspect and does not run a unique index [11], so if uniqueness enforcement is your motive, check it against your own object before the DDL goes in.
And if index-only scans are the motive, the plain multi-column index delivered one six major releases before INCLUDE existed [4][3]. Oracle users, Pachot notes, built covering indexes the same way for decades with no equivalent clause at all [10].
Ranked by verification strength, evidence, and original report placement.
The test table is demo (id text primary key, value text), loaded with 1,000,000 rows from generate_series(1,1000000) where id = md5(g::text) and value = md5((g%1000)::text).
With index demo_idx1 as btree(value, id), the query "select id from demo where value <= '01'" ran as Index Only Scan using demo_idx1, cost=0.55..566.29 rows=8785 width=33, actual rows=4000, Heap Fetches: 0, Buffers: shared hit=50, Execution Time 0.710 ms.
Index-only scans for B-tree indexes were introduced in PostgreSQL 9.2, and the INCLUDE clause came later, in PostgreSQL 11.
With index demo_idx2 as btree(value) INCLUDE (id), the same query ran as Index Only Scan using demo_idx2 with cost=0.55..566.29 rows=8785, actual rows=4000, Heap Fetches: 0, Buffers: shared hit=50, Execution Time 1.323 ms.
pg_relation_size reports both demo_idx1 and demo_idx2 at 91 MB.
Both indexes store value and id in their leaf tuples, so both can support index-only scans; demo_idx1's ordering is based on (value, id, TID) with both user columns participating in navigation, while demo_idx2's ordering is based on (value, TID) and id is stored as additional payload, absent from pivot tuples and other upper B-tree levels.
Distinct publishers with included, body-backed reporting in this cluster.
dev.to
1 article · August 31, 2026
Follow any of these and your For You feed starts watching them — no settings page required.
build
Why an uncommitted DELETE of a million rows is invisible: nothing was ever deleted1 distinct publisher
build
OrioleDB's real argument: MVCC has to version the index, not just the row1 distinct publisher
build
Your ORM never puts a WHERE clause in an index, and that is where the seq scans live1 distinct publisher
build
Notion's agent stack is live, not slideware, and it only changes one of your decisions1 distinct publisher
Evidence-backed comparisons of source perspectives and observed adoption signals. Read the methodology
Which Builder, Operator, and Investor concerns the observed source mix emphasized—not a truth score.
Evidence, demonstrated adoption, hype gap, incentives, and confidence are assessed independently, each on its own current evidence. How these are measured.
Reproducible on a laptop
Every figure arrives with the statement that produced it - the table definition, the generate_series load, two EXPLAIN (verbose, analyze, buffers) plans, pg_relation_size, two pg_index queries, a pageinspect dump of an internal page. That is a stronger form of sourcing than a citation, because a reader with any recent Postgres can rerun the lot. What holds the score down is what the transcript never prints: the server version, a second timing run, and the last third of its own page-level argument, which stops inside an unfinished query.
Not measured
Nothing in this reporting speaks to who uses INCLUDE or how. One table on one unnamed machine tells us how two index shapes behaved for one query; there are no counts of INCLUDE indexes in real schemas, no upgrade or migration evidence, no team saying it changed their index choices. Treating the 1.9x timing spread as a signal about practice would be reading a micro-benchmark as a survey.
Shows more than it claims
The headline point - Postgres was covering queries six major releases before INCLUDE existed - is proved outright by the post's own first plan, and the structural payoff is dumped on the page as catalogue rows and page bytes rather than declared. The piece even declines to draw the conclusion its own numbers invite about the slower INCLUDE run. One sentence does run ahead of the evidence, the claim that the planner switched because cost was slightly lower when both plans print the same cost, but on balance the demonstration over-delivers on the rhetoric.
Little pull on the numbers
A personal Postgres notebook on a developer publishing platform: nothing for sale, no vendor benchmark being defended, and the target of the correction is Postgres folklore rather than a competitor. The one lean worth naming is comparative - the aside that Oracle users have added extra index columns for decades shapes which question gets asked, and it is a question an Oracle-shaped reader would ask - but it does not touch any figure reported.
Checkable but uncorroborated
One publisher, one machine, one run per index - and yet the claims are the sort that survive scrutiny, because the instructions for scrutinising them are in the text. The middling score comes from absences rather than doubts: no version banner, no repeated timings, no unique-index case, one asserted cost difference contradicted by the printed plans, and an argument that literally halts mid-statement.