Smaller, sharper Postgres indexes: partial indexes, INCLUDE columns and index-only scans
Index only the rows you query, let an index answer a query without touching the table, and know what the visibility map has to do with it.

Most Postgres indexes are plain: one or more columns, every row in the table. That is a fine default, and the general guide to indexes covers why. But two less-used features can make an index smaller and a query cheaper at the same time. One is the partial index. The other is the covering index, built with INCLUDE.
Both come straight from the PostgreSQL documentation, and both reward a bit of care.
The problem with indexing every row
Picture a jobs table with ten million rows. Almost all of them are done. The few thousand that are pending are the only ones your workers ever look for.
A normal index on (created_at) stores all ten million entries. Every insert and every status update has to maintain it, and it takes space in memory that something more useful could have used. You only ever search the small pending slice.
Partial indexes: index the rows you ask about
A partial index has a WHERE clause, called its predicate. Rows that do not satisfy it are simply not in the index.
CREATE INDEX jobs_pending_idx
ON jobs (created_at)
WHERE status = 'pending';
Now the index holds only pending jobs. It is tiny, it stays in cache, and a row that moves from pending to done leaves it.
The documentation lists three situations where this pays off:
- Skipping very common values. If a value appears in a large share of the rows, an index scan rarely beats a sequential scan for it, so indexing those rows is wasted effort.
- Skipping rows nobody searches for, like the
donejobs above or billed orders. - Uniqueness on a subset. A unique partial index only constrains the rows that match.
The third one is a favourite for soft deletes:
CREATE UNIQUE INDEX users_email_live_idx
ON users (email)
WHERE deleted_at IS NULL;
Two deleted accounts may share an email, but two live ones may not. You get that rule in the database, where it cannot be forgotten.
The planner has to prove it can use the index
A partial index is only usable when the query's WHERE clause implies the index predicate. Postgres can work out simple cases, such as knowing that x < 1 implies x < 2, but beyond that you should assume the predicate must appear in the query almost word for word.
This matters in two ways:
- A query that does not mention
status = 'pending'cannot usejobs_pending_idx, because the index does not know about the other rows. - Matching happens when the query is planned. If you write
WHERE status = $1, the planner cannot know that$1will be'pending', so a generic plan will not use the index. Put the constant in the query text, or make sure your driver does not force a generic plan for that statement.
Tip
After creating a partial index, run the query you built it for under
EXPLAIN. If the index does not appear, the predicate and the query probably do not line up. The post on reading EXPLAIN ANALYZE shows what to look for.
What not to do with them
The documentation is candid that the benefit is often modest, because you are claiming to know better than the planner. It also warns against a particular misuse: creating one partial index per category, such as WHERE category = 1, WHERE category = 2 and so on. The planner has to test each of them. A single index on (category, data) is usually better, and for truly large tables the right tool is partitioning, where the system knows the pieces do not overlap.
Also remember that predicates tied to specific values need upkeep. If the "common value" you excluded changes over time, the index may need to be rebuilt.
Index-only scans: answer from the index alone
Normally an index scan finds matching entries and then visits the table (the heap) to fetch each row. That is random I/O, and it is often the most expensive part.
If every column the query needs is already in the index, Postgres can skip the heap. This is an index-only scan.
With an index on (x, y):
-- can be an index-only scan
SELECT x, y FROM tab WHERE x = 'key';
-- cannot: z is not in the index
SELECT x, z FROM tab WHERE x = 'key';
B-tree indexes always support this. GIN indexes do not, because each entry holds only part of the original value.
INCLUDE: carry extra columns without making them part of the key
Sometimes you want a column in the index only so it can be returned, not so you can search on it. That is what INCLUDE is for:
CREATE INDEX orders_customer_idx
ON orders (customer_id)
INCLUDE (total_minor);
A query such as SELECT total_minor FROM orders WHERE customer_id = $1 can now be answered from the index. The included column is payload: it is not used for searching or ordering, so its type does not need to be one the index can compare.
Uniqueness applies only to the key columns. This is valid and enforces uniqueness on x alone:
CREATE UNIQUE INDEX tab_x_y ON tab (x) INCLUDE (y);
INCLUDE also works in UNIQUE and PRIMARY KEY constraints. Before it existed, people appended extra key columns, which could not enforce uniqueness on just the real key.
Warning
Be conservative with payload columns. Wide values can exceed the maximum index tuple size, and then inserts fail. They also duplicate data and make the index bigger, which can slow searches down.
The catch: the visibility map
Index entries do not carry the information Postgres needs to know whether a row is visible to your transaction. So an index-only scan checks the table's visibility map, which has one bit per heap page.
- If the bit is set, every row on that page is visible to everyone, and the answer comes straight from the index.
- If it is not set, Postgres still has to visit the heap page, and you gain nothing over a normal index scan.
The visibility map is tiny, roughly four orders of magnitude smaller than the heap, so it usually sits in memory. But the trick only works well when a large share of pages are all-visible. That is common for tables whose rows rarely change, and rare for tables with constant updates. Vacuum is what sets those bits, so regular maintenance matters.
EXPLAIN (ANALYZE, BUFFERS)
SELECT total_minor FROM orders WHERE customer_id = 42;
Look for Index Only Scan and a low Heap Fetches number. A high count means the visibility map is not helping yet.
A short decision guide
| Situation | Reach for |
|---|---|
| Queries only ever target a small slice of rows | Partial index |
| A rule should hold only for some rows | Unique partial index |
| A hot query reads one or two extra columns | INCLUDE covering index |
| The table is updated constantly | Probably neither; check heap fetches first |
Try it safely
Pick one slow, frequent query. Write down the exact columns it reads and the rows it targets. Create the candidate index in a test copy of the data, compare EXPLAIN before and after, and look at the index size with \di+. If it is not clearly smaller or faster, drop it. An index you cannot justify is a cost on every write.


