Index Only Scan

Description

Uses index values for the result and checks tuple visibility, visiting the heap when the visibility map does not suffice.

Core node tag

T_IndexOnlyScan

Structured EXPLAIN Node Type

Index Only Scan

Inputs

An index, relation and visibility information

Output

Qualified tuples from index values

Executor initializer

ExecInitIndexOnlyScan

Memory mechanism

unclassified

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Index Only Scan.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: ExecIndexOnlyScanEstimate, ExecIndexOnlyScanInitializeDSM, ExecIndexOnlyScanInitializeWorker, ExecIndexOnlyScanReInitializeDSM, ExecIndexOnlyScanRetrieveInstrumentation.

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

In the example above, the ALL operator runs the subplan again for each row of the outer query (which accounts for the high estimated cost). Some queries can use a hashed subplan to avoid that:

EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on tenk1 t  (cost=61.77..531.77 rows=5000 width=244)
   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Index Only Scan using onek_unique1 on onek o  (cost=0.28..59.27 rows=1000 width=4)
(4 rows)

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

The “ Index Searches ” line is also useful with B-tree index scans that apply the skip scan optimization to more efficiently traverse through an index:

EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tenk1_four_unique1_idx on tenk1  (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
   Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42))
   Heap Fetches: 0
   Index Searches: 3
   Buffers: shared hit=7
 Planning Time: 0.029 ms
 Execution Time: 0.012 ms

Executor implementation notes

Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.

We reach here if the index only scan is not parallel, or if we're serially executing an index only scan that was planned to be parallel.

If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM.

We can skip the heap fetch if the TID references a heap page on which all tuples are known visible to everybody. In any case, we'll use the index tuple not the heap tuple as the data source.

Note on Memory Ordering Effects: visibilitymap_get_status does not lock the visibility map buffer, and therefore the result we read here could be slightly stale. However, it can't be stale enough to matter.

EXPLAIN identity in core source

case T_IndexOnlyScan:
			pname = sname = "Index Only Scan";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_indexonlyscan
• random_page_cost
• Index AM

Documentation and source

• src/backend/commands/explain.c:1447
• src/backend/executor/execProcnode.c:224
• src/backend/executor/nodeIndexonlyscan.c
• src/include/nodes/plannodes.h
• PostgreSQL 18.6 · using-explain
• PostgreSQL 18.6 · using-explain

Compare versions

PostgreSQL 17 → 18: changed.

--- PostgreSQL 17
+++ PostgreSQL 18
@@ -6,7 +6,8 @@
     "ExecIndexOnlyScanEstimate",
     "ExecIndexOnlyScanInitializeDSM",
     "ExecIndexOnlyScanInitializeWorker",
-    "ExecIndexOnlyScanReInitializeDSM"
+    "ExecIndexOnlyScanReInitializeDSM",
+    "ExecIndexOnlyScanRetrieveInstrumentation"
   ],
   "partial_modes": [],
   "strategies": [],

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• Bitmap Heap ScanBitmapHeapScan
• CTE ScanCteScan
• Function ScanFunctionScan
• Index ScanIndexScan
• Named Tuplestore ScanNamedTuplestoreScan
• Sample ScanSampleScan
• Seq ScanSeqScan
• Subquery ScanSubqueryScan
• Table Function ScanTableFuncScan
• Tid Range ScanTidRangeScan
• Tid ScanTidScan
• Values ScanValuesScan