Franck Pachot

Posted on Aug 31 Edited on Sep 5

PostgreSQL INCLUDE indexes: what problem are they trying to solve?

#postgres #sql #database #index

Clarifies pre-v11 covering index capabilities

A common way to explain PostgreSQL's INCLUDE clause is that it helps with index-only scans. That's true, but it misses an important detail. Even before PostgreSQL 11, which introduced the INCLUDE clause, PostgreSQL was already capable of supporting covering indexes:

postgres=# create table demo ( id text primary key, value text)
;
CREATE TABLE

postgres=# insert into demo(id, value)
           select
             md5(g::text),       -- using text to see it with pageinspect
             md5((g%1000)::text) -- using text to see it with pageinspect
from generate_series(1,1000000) g
;
INSERT 0 1000000

postgres=# create index demo_idx1
           on demo(value, id)
;
CREATE INDEX

postgres=# vacuum analyze
;
VACUUM

postgres=# explain (verbose, analyze, buffers)
select id from demo
  where value <= '01'
;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_idx1 on public.demo  (cost=0.55..566.29 rows=8785 width=33) (actual time=0.008..0.551 rows=4000.00 loops=1)
   Output: id
   Index Cond: (demo.value <= '01'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=50
 Query Identifier: -9119705436468283309
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.118 ms
 Execution Time: 0.710 ms

The Index Only Scan covers all filtering (Index Cond: (demo.value <= '01'::text)) and projection (Output: id) without an INCLUDE clause.

Here is another example with output in PostgreSQL 8.4 and 9.3. Index-only scan for B-tree indexes was introduced in 9.2, and INCLUDE came later in 11.

This proves that you don't need an INCLUDE clause to create a covering index for a query. The same is true for other databases: Oracle users have built covering indexes that way for decades without an equivalent to the INCLUDE clause. Adding extra columns to the index can eliminate table access when the query needs those column values.

So why did PostgreSQL introduce a new syntax?

Let's create the covering index using an INCLUDE clause:

postgres=# create index demo_idx2
  on demo(value) include (id)
;
CREATE INDEX

The planner chooses it because the estimated cost is slightly lower, although the difference is too small to appear in the rounded cost shown by EXPLAIN:

postgres=# explain (verbose, analyze, buffers)
select id from demo
  where value <= '01'
;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_idx2 on public.demo  (cost=0.55..566.29 rows=8785 width=33) (actual time=0.015..1.011 rows=4000.00 loops=1)
   Output: id
   Index Cond: (demo.value <= '01'::text)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=50
 Query Identifier: -9119705436468283309
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.132 ms
 Execution Time: 1.323 ms
(11 rows)

The two indexes have the same size in this example:

postgres=# select relname, pg_size_pretty(pg_relation_size(oid))
           from pg_class where relname in ('demo_idx1','demo_idx2')
;
  relname  | pg_size_pretty
-----------+----------------
 demo_idx2 | 91 MB
 demo_idx1 | 91 MB

Both indexes store value and id in their leaf tuples, so both can support index-only scans. The difference is in the B-tree key space. For demo_idx1, ordering is based on (value, id, TID), so both user columns participate in navigation. For demo_idx2, ordering is based on (value, TID), and id is stored as additional payload and is therefore absent from pivot tuples and other upper B-tree levels.

This is visible in the number of index key attributes:

postgres=# select indnatts, indnkeyatts from pg_index 
           where indexrelid='demo_idx1'::regclass
;
 indnatts | indnkeyatts
----------+-------------
        2 |           2


postgres=# select indnatts, indnkeyatts from pg_index 
           where indexrelid='demo_idx2'::regclass
;
 indnatts | indnkeyatts
----------+-------------
        2 |           1

Note (and see Vinh's comment below): the difference is only in the internal B-tree pages, not in the leaves. Since internal pages represent a small fraction of the index, the size difference is tiny (here 11620 vs. 11670 pages) and not visible in the rounded cost displayed by EXPLAIN. More importantly, this difference is generally not a reason to choose INCLUDE instead of adding the column to the key. The primary reasons are the different semantics and behavior: uniqueness enforcement, sort order, operator class requirements, deduplication, and update/maintenance characteristics.

Pageinspect can show more details

I check the index names and enable the extension:

postgres=# \d demo

              Table "public.demo"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 id     | text |           | not null |
 value  | text |           |          |
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)
    "demo_idx1" btree (value, id)
    "demo_idx2" btree (value) INCLUDE (id)

postgres=# create extension if not exists pageinspect
;
CREATE EXTENSION

Here are some entries from an internal page of the B-tree with all columns in the key:

postgres=# select * from bt_multi_page_stats('demo_idx1', 1, -1)     
           where type='i' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx1', :blkno) order by itemoffset limit 4
;

 itemoffset |   ctid    | itemlen | nulls | vars | dead | htid |                                          encode                                           |                                                                                                          data
------------+-----------+---------+-------+------+------+------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (11666,0) |       8 | f     | f    |      |      |                                                                                           |
          2 | (11667,2) |      80 | f     | t    |      |      | ffeabd223de0d4eacb9a3e6e53e5448dCe370679455dd6faf22304e9943c1f2fa\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 65 33 37 30 36 37 39 34 35 35 64 64 36 66 61 66 32 32 33 30 34 65 39 39 34 33 63 31 66 32 66 61 00 00 00 00 00 00
          3 | (11668,2) |      80 | f     | t    |      |      | ffeabd223de0d4eacb9a3e6e53e5448dCf9ae9b3ef06aa649c69149c580697d44\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 39 61 65 39 62 33 65 66 30 36 61 61 36 34 39 63 36 39 31 34 39 63 35 38 30 36 39 37 64 34 34 00 00 00 00 00 00
(3 rows)

You can recognize the same value (ffeabd223de0d4eacb9a3e6e53e5448d) for two id (e370679455dd6faf22304e9943c1f2fa and f9ae9b3ef06aa649c69149c580697d44)

Here are entries from a leaf page of this index:

postgres=# select * from bt_multi_page_stats('demo_idx1', 1, -1)     
           where type='l' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx1', :blkno) order by itemoffset limit 4
;
 itemoffset |   ctid    | itemlen | nulls | vars | dead |   htid    |                                          encode                                           |                                                                                                          data
------------+-----------+---------+-------+------+------+-----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (2574,81) |      80 | f     | t    | f    | (2574,81) | ffeabd223de0d4eacb9a3e6e53e5448dCf9ae9b3ef06aa649c69149c580697d44\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 39 61 65 39 62 33 65 66 30 36 61 61 36 34 39 63 36 39 31 34 39 63 35 38 30 36 39 37 64 34 34 00 00 00 00 00 00
          2 | (10612,3) |      80 | f     | t    | f    | (10612,3) | ffeabd223de0d4eacb9a3e6e53e5448dCfa133607fa7a0fc0aa0c4eb5af973b0e\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 31 33 33 36 30 37 66 61 37 61 30 66 63 30 61 61 30 63 34 65 62 35 61 66 39 37 33 62 30 65 00 00 00 00 00 00
          3 | (1291,4)  |      80 | f     | t    | f    | (1291,4)  | ffeabd223de0d4eacb9a3e6e53e5448dCfa684bc1a44ca31270e620437a302582\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 36 38 34 62 63 31 61 34 34 63 61 33 31 32 37 30 65 36 32 30 34 33 37 61 33 30 32 35 38 32 00 00 00 00 00 00
          4 | (2994,61) |      80 | f     | t    | f    | (2994,61) | ffeabd223de0d4eacb9a3e6e53e5448dCfa81207e0ef8bf7c472f22d1093f9d8c\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 66 61 38 31 32 30 37 65 30 65 66 38 62 66 37 63 34 37 32 66 32 32 64 31 30 39 33 66 39 64 38 63 00 00 00 00 00 00
(4 rows)

You can recognize similar data. The leaves simply add the TID, which is exposed here as htid (heap tuple identifier). The entries are ordered by (value, id):

 fe9fc289c3ff0af142b6d3bead98a923 c8ea21e50b29b5e7081dd060e311fc7f (2574,81)
 fe9fc289c3ff0af142b6d3bead98a923 b22ba7ef4b85c722a92da83a480dd63f (10612,3)
 fe9fc289c3ff0af142b6d3bead98a923 b24f3bf60138d0b322d72be638626170 (1291,4)
 fe9fc289c3ff0af142b6d3bead98a923 b35b3291bb326500fbf6237f593f56ff (2994,61)

Let's look at the other index where id is in INCLUDE rather than the key. The leaves have similar length and format:

postgres=# select * from bt_multi_page_stats('demo_idx2', 1, -1)     
           where type='l' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx2', :blkno) order by itemoffset limit 4
;
 itemoffset |    ctid    | itemlen | nulls | vars | dead |    htid    |                                          encode                                           |                                                                                                          data
------------+------------+---------+-------+------+------+------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 | (12081,14) |      80 | f     | t    | f    | (12081,14) | ffeabd223de0d4eacb9a3e6e53e5448dCe54b7a956b88f1a26234f9666332f40f\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 65 35 34 62 37 61 39 35 36 62 38 38 66 31 61 32 36 32 33 34 66 39 36 36 36 33 33 32 66 34 30 66 00 00 00 00 00 00
          2 | (12093,42) |      80 | f     | t    | f    | (12093,42) | ffeabd223de0d4eacb9a3e6e53e5448dC65e6c0b6c51f50809d390bd6af75262f\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 36 35 65 36 63 30 62 36 63 35 31 66 35 30 38 30 39 64 33 39 30 62 64 36 61 66 37 35 32 36 32 66 00 00 00 00 00 00
          3 | (12105,70) |      80 | f     | t    | f    | (12105,70) | ffeabd223de0d4eacb9a3e6e53e5448dC0053cd459922f1a843b5af3e5b384c61\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 30 30 35 33 63 64 34 35 39 39 32 32 66 31 61 38 34 33 62 35 61 66 33 65 35 62 33 38 34 63 36 31 00 00 00 00 00 00
          4 | (12118,17) |      80 | f     | t    | f    | (12118,17) | ffeabd223de0d4eacb9a3e6e53e5448dC37b9ccdafd5e83b27cf23bb1e0089e63\000\000\000\000\000\000 | 43 66 66 65 61 62 64 32 32 33 64 65 30 64 34 65 61 63 62 39 61 33 65 36 65 35 33 65 35 34 34 38 64 43 33 37 62 39 63 63 64 61 66 64 35 65 38 33 62 32 37 63 66 32 33 62 62 31 65 30 30 38 39 65 36 33 00 00 00 00 00 00
(4 rows)

However, can see that the entries are ordered by (value, TID) even if id values are present in the index entries:

 ffeabd223de0d4eacb9a3e6e53e5448d (12081,14) e54b7a956b88f1a26234f9666332f40f
 ffeabd223de0d4eacb9a3e6e53e5448d (12093,42) 65e6c0b6c51f50809d390bd6af75262f
 ffeabd223de0d4eacb9a3e6e53e5448d (12105,70) 0053cd459922f1a843b5af3e5b384c61
 ffeabd223de0d4eacb9a3e6e53e5448d (12118,17) 37b9ccdafd5e83b27cf23bb1e0089e63

That's one physical difference between the two indexes: the sort order. The columns in INCLUDE are not part of the key. They are just additional payload. This has some pros and cons that we will cover later.

There's another difference in the B-tree internal pages (root and branches) which store key ranges:

postgres=# select * from bt_multi_page_stats('demo_idx2', 1, -1)     
           where type='i' order by blkno desc limit 1
\gset
postgres=# select itemoffset, ctid, itemlen, nulls, vars, dead, htid, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape'), data
           from bt_page_items('demo_idx2', :blkno) order by itemoffset limit 4
;
 itemoffset |     ctid     | itemlen | nulls | vars | dead |    htid    |                            encode                            |                                                          data          
------------+--------------+---------+-------+------+------+------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------
          1 | (11524,0)    |       8 | f     | f    |      |            |                                                              |
          2 | (11525,4097) |      56 | f     | t    |      | (10941,34) | fe131d7f5a6b38b23cc967316c13dae2\000\000\000\000\000\000\000 | 43 66 65 31 33 31 64 37 66 35 61 36 62 33 38 62 32 33 63 63 39 36 37 33 31 36 63 31 33 64 61 65 32 00 00 00 00 00 00 00
          3 | (11526,4097) |      56 | f     | t    |      | (12015,40) | fe131d7f5a6b38b23cc967316c13dae2\000\000\000\000\000\000\000 | 43 66 65 31 33 31 64 37 66 35 61 36 62 33 38 62 32 33 63 63 39 36 37 33 31 36 63 31 33 64 61 65 32 00 00 00 00 00 00 00
          4 | (11528,4097) |      56 | f     | t    |      | (744,66)   | fe73f687e5bc5280214e0486b273a5f9\000\000\000\000\000\000\000 | 43 66 65 37 33 66 36 38 37 65 35 62 63 35 32 38 30 32 31 34 65 30 34 38 36 62 32 37 33 61 35 66 39 00 00 00 00 00 00 00
(4 rows)

Only the key columns have values in branch pages, not the included columns, because the latter aren't needed to navigate to a key. This keeps upper-level tuples smaller. PostgreSQL can often achieve a similar effect through suffix truncation, which removes trailing key columns from upper B-tree levels when they are not needed to distinguish key ranges. However, defining a column as INCLUDE guarantees that it never participates in navigation and never appears in upper levels.

Columns in INCLUDE vs. in key

The real reason INCLUDE exists is not visible here. PostgreSQL enforces unique constraints and primary keys through unique indexes. You cannot add extra columns to the key without changing the uniqueness definition, so you must add them with INCLUDE so they don't participate in the B-tree key. This also explains why Oracle didn't need to implement an equivalent of INCLUDE: Oracle non-unique B-trees can enforce a unique constraint as long as the unique key is a prefix of the index key.

Another advantage of PostgreSQL's extensibility of data and index types is that, unlike key columns, included columns don't require an operator class for the access method, so you can include columns of types with no defined operator class at all.

INCLUDE columns also receive a maintenance-side benefit: updates that touch only the included column(s) can qualify for PostgreSQL's bottom-up index deletion optimization, which plain key-column updates cannot.

But there are also limitations where you may have to add the column to the key. For example, expressions can't be used as included columns. And if you want to cover the ORDER BY in addition to the WHERE and SELECT, the columns have to be in the key. B-tree deduplication is disabled entirely on any index with a non-key column. This means that an index with INCLUDE may be larger than a comparable plain B-tree even before accounting for the bytes needed to store the included column itself, because it also loses posting-list compression opportunities that would otherwise be available.

Index types

While all examples in this article use B-tree indexes, PostgreSQL also supports INCLUDE with GiST and SP-GiST indexes. This article's discussion of key space, pivot tuples, suffix truncation, deduplication, and uniqueness enforcement is specific to B-tree implementation details.

Top comments (7)

Ben Halpern • Sep 2

Great writeup

dinesh reddy • Sep 1

Frank since you are microsoft guy i expect lot of benchmarks between horizondb and interesting insights from horizondb and comparisons with aws aurora..any plans to do so?

Franck Pachot • Sep 1

I will, probably. But it will take time. I don't want to test a biased workload that I know hits all optimizations in HorizonDB (e.g pgvector vs DiskANN would be an easy win with pre-filtering). And I need to have meaningful metrics in both. Comparing time is not sufficient without understanding, and would require long runs with high throughput that can burn my AWS credits too fast 😂
And we need to compare at same cost and HorizonDB is still in preview.
Which kind of benchmark would you think is interesting?

Kane Lim • Aug 31

Hello Glad to see you, I am Kane Lim from Hong Kong. I have over 10 years of development experience. I am writing this because your post was interesting.

The distinction between covering an index and INCLUDE is often misunderstood. Your explanation correctly gets to the B tree internals: INCLUDE columns are payload, not navigation keys. That matters beyond index only scans because it changes uniqueness semantics, upper level tuple size, ordering behavior, and maintenance characteristics.

One optimization I would emphasize further is workload driven index design. Before adding INCLUDE columns, inspect pg_stat_user_indexes, EXPLAIN BUFFERS, visibility map coverage, write amplification, and index bloat. An index that eliminates heap fetches but increases WAL volume and prevents B tree deduplication can actually degrade the system under write heavy workloads.

For unique indexes especially, INCLUDE gives PostgreSQL a clean separation between identity and retrieval data. I would also test HOT eligibility and bottom up index deletion under realistic UPDATE patterns rather than assuming the covering index is automatically superior.

Excellent deep dive into what INCLUDE actually changes physically. Posts like this are exactly the database internals discussions I enjoy.

Vinh Nguyen • Sep 4

The two plans you pasted do not show the cost difference the sentence claims. Both read cost=0.55..566.29, both Buffers: shared hit=50, both Heap Fetches: 0, and pg_relation_size puts both indexes at 91 MB - so at the precision EXPLAIN prints, the estimates are identical, and the only number that does differ is execution time, which goes the other way (0.710 ms against 1.323 ms). That matters for the argument rather than against it: a reader who takes "the planner chooses it because the estimated cost is slightly lower" at face value has been handed the payload-versus-key-space distinction as a cost story, when what the output supports is a B-tree structure story - indnkeyatts 1 against 2, and id absent from the pivot tuples. Making the cost claim readable needs each index visible on its own, so dropping or hiding demo_idx1 and printing the plan again, because with both present a difference below 0.005 does not appear and nothing in the output distinguishes a cheaper estimate from a tie.

Franck Pachot • Sep 5

You are right. The cost shown by EXPLAIN is rounded, so my statement wasn't supported by the output I pasted. And pg_relation_size(), rounds both to 91 MB, but the slight difference is visible in the page count:

postgres=# select relname, pg_size_pretty(pg_relation_size(oid))
           from pg_class where relname in ('demo_idx1','demo_idx2')
;
  relname  | pg_size_pretty
-----------+----------------
 demo_idx1 | 91 MB
 demo_idx2 | 91 MB
(2 rows)

postgres=# select relname, reltuples, relpages from pg_class where relname like 'demo%';
  relname  | reltuples | relpages
-----------+-----------+----------
 demo      |     1e+06 |    12346
 demo_idx1 |     1e+06 |    11670
 demo_idx2 |     1e+06 |    11620
 demo_pkey |     1e+06 |     9348
(4 rows)

Both indexes store the same payload in leaf pages, but demo_idx2 has smaller pivot tuples because id is not part of the navigation key and is absent from branch pages. The benefit is therefore in the upper B-tree levels rather than in the leaves. Thanks, I'll add this precision

Vinh Nguyen • Sep 5

If the leaves are identical then all 50 pages sit above them, and that makes the delta close to the entire upper structure rather than a slice of it. Two 33-byte md5 pivots against one puts fanout somewhere around 100 against 180 on an 8 KB page, so level 1 alone comes out near 115 pages for demo_idx1 against 63 for demo_idx2 - a predicted gap of about 50, which is what relpages shows, so the mechanism is saturated at this key width rather than partly exercised. The precision worth adding alongside it is the ceiling: everything above the leaves is around 1% of an index this shape, so no MB-granularity size output can show it by construction, and Buffers: shared hit=50 being identical in both plans is the same bound on the read side - the shorter pivots pay off only when they remove a descent page, which is a step and not a proportion.