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:
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
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.
