Application developmentDive 01 of 02

What PostgreSQL does with one SELECT

Follow a recent-orders query from cardinality estimates to a B-tree leaf and the heap. Change the data and index, inspect actual plans and see why an index-only scan can still fetch a row.

Levels
8
Reading
36 min
Depth
Idea to System
Published
Recent orders for account 42
SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;
  1. Estimate30 matching orders
  2. LocateB-tree root to leaf
  3. VerifyVisibility in the snapshot
  4. ReturnAt most 20 rows

Level 01/08Idea

Twenty rows from an uneven table

An orders screen asks for the latest twenty orders for an account. The query looks small. It names one table, fixes an account, sorts by time and returns twenty rows. On disk, however, the database could have to visit the entire table to discover those twenty. With the right index it can start near the newest matching entry and stop as soon as the limit is satisfied. The first question is what PostgreSQL can prove about the order of the rows it reads.

In our scratch database, the orders arrive in timestamp order. Account 1 owns 144,000 of 180,000 orders, with smaller accounts mixed among its rows. Account 42 owns just 30 rows spread over the table. We also build a comparison table where account IDs are assigned evenly. Account 1 has 149 rows in that table, so the account number stays constant while its frequency changes.

The query specifies created_at DESC, id DESC. The second sort key breaks ties, so there is a defined order even if two orders share a timestamp. The seed uses a distinct timestamp for each row to make the fixture easy to reproduce. Either way, the result is the twenty rows visible to this statement’s snapshot, in that order.

Read the table and seed SQL
orders.sql
CREATE EXTENSION pageinspect;
CREATE EXTENSION pg_visibility;

CREATE TABLE orders (
  id integer PRIMARY KEY,
  account_id integer NOT NULL,
  created_at timestamptz NOT NULL,
  total_cents integer NOT NULL,
  state text NOT NULL,
  note text NOT NULL
) WITH (fillfactor = 90);

INSERT INTO orders
SELECT
  i,
  CASE WHEN i % 5 = 0 THEN 2 + (i / 5) % 1200 ELSE 1 END,
  timestamptz '2026-01-01 00:00:00+00' + i * interval '1 second',
  100 + i % 20000,
  CASE WHEN i % 5 = 0 OR i % 101 = 0 THEN 'pending' ELSE 'paid' END,
  repeat('x', 64)
FROM generate_series(1, 180000) AS g(i);

ALTER TABLE orders ALTER COLUMN account_id SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN state SET STATISTICS 1000;
ANALYZE orders;

Level 02/08Interface

From a statement to a plan tree

A client sends SQL over its connection to a PostgreSQL backend. The raw parser recognizes the structure of the statement. Parse analysis resolves the table, columns and operators against the catalogs. The rewrite system applies rules such as expansion of a view. The planner then reasons about legal ways to obtain the result, estimates their costs and constructs a plan for the executor.

For our single-table query, an access path can read heap pages in physical order, use an index to fetch candidate tuple IDs, or build a bitmap of heap locations. Each path has consequences beyond finding rows. A B-tree on account_id groups matching entries, but it does not supply the requested ordering on created_at and id. A B-tree with all three keys can satisfy the filter and the order together.

The executor runs a tree of plan nodes. Read it from the root to see what each node asks its children for. A Limit pulls rows from its child until it has enough. A Sort generally has to consume its input before it can return the first correctly ordered row. A top-N sort may keep only its best candidates, but it still examines all rows passed to it. With an ordered index child, the executor can stop asking for tuples when the twentieth row arrives.

Execute the query and capture its plan
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

EXPLAIN alone reports estimates. With ANALYZE, PostgreSQL runs the statement and reports what happened. Plan Rows is the planner’s estimate of how many rows a node emits if run to completion. Actual Rows is the measured output per loop. A scan below Limit can estimate thirty possible matches and emit twenty, simply because the parent stops reading. That difference by itself is no evidence of a bad estimate.

Estimated cost has planner units, with startup and total components. A plan that can return its first rows cheaply may suit a small limit even if reading every matching row would be expensive. Execution time in milliseconds is measured separately. TIMING OFF keeps row and buffer instrumentation while disabling individual node timing, which reduces overhead when nodes are called many times.

The path of a query · Reading EXPLAIN

Check yourselfA scan under Limit estimated 30 rows and emitted 20. What should you check first?

Level 03/08Runtime

Change the account and the index

Start with the account index and switch between accounts 42 and 1 on the skewed data. Both statements have the same shape, but account 42 filters almost everything out. With the equality index, PostgreSQL can identify its scattered heap locations without scanning the table. For the hot account, visiting most of the table through index entries is expensive. See which scan the planner actually selects.

Now keep account 1 and switch to the uniform data. This comparison changes the frequency of the same key, keeping the statement shape and the index strategy. Finally add the ordered key. The key that leads with account_id can also deliver rows in the required order within that account, regardless of how large its share of the table is.

Data and account
Available indexes

30 orders match the account before LIMIT.

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;
Shared hits
36
Shared reads
2
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Limit
20201
SortSort method quicksort
30201
Index Scanorders_account_idxIndex condition (account_id = 42)
30301
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 77.84,
      "Total Cost": 77.89,
      "Plan Rows": 20,
      "Plan Width": 16,
      "Actual Rows": 20,
      "Actual Loops": 1,
      "Shared Hit Blocks": 36,
      "Shared Read Blocks": 2,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Sort",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Startup Cost": 77.84,
          "Total Cost": 77.91,
          "Plan Rows": 30,
          "Plan Width": 16,
          "Actual Rows": 20,
          "Actual Loops": 1,
          "Sort Key": [
            "created_at DESC",
            "id DESC"
          ],
          "Sort Method": "quicksort",
          "Sort Space Used": 27,
          "Sort Space Type": "Memory",
          "Shared Hit Blocks": 36,
          "Shared Read Blocks": 2,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0,
          "Plans": [
            {
              "Node Type": "Index Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Scan Direction": "Forward",
              "Index Name": "orders_account_idx",
              "Relation Name": "orders",
              "Alias": "orders",
              "Startup Cost": 0.29,
              "Total Cost": 77.1,
              "Plan Rows": 30,
              "Plan Width": 16,
              "Actual Rows": 30,
              "Actual Loops": 1,
              "Index Cond": "(account_id = 42)",
              "Rows Removed by Index Recheck": 0,
              "Shared Hit Blocks": 30,
              "Shared Read Blocks": 2,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0
            }
          ]
        }
      ]
    },
    "Planning": {
      "Shared Hit Blocks": 119,
      "Shared Read Blocks": 1,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 1.507,
    "Triggers": [],
    "Execution Time": 0.54
  }
]
Nine PostgreSQL captures. The controls select a saved plan for the data distribution, account and available indexes. Expand a plan to inspect the original JSON.

For the account-only index, the skewed account 42 capture uses an index scan, the uniform account 1 capture uses a bitmap heap scan, and the skewed account 1 capture uses a sequential scan. The ordered index makes the hot account’s scan estimate 144,000 matching rows but deliver just twenty to the limit. That difference comes from early termination. The scan has no obligation to consume the rest of its range.

Why the account index can produce a bitmap

A bitmap index scan collects heap tuple locations. A bitmap heap scan then visits heap pages in block order, which avoids bouncing between pages in the order of index entries. That can save random access when enough rows match to make many scattered heap visits worthwhile. The bitmap does not carry the requested result order, so sorting is still necessary. A memory-constrained bitmap can also fall back to page-level entries and recheck the condition on tuples it reads.

A sequential scan reads heap pages in physical order and applies the account filter after visits to the table. The ordered index, by contrast, can keep the account comparison as an Index Cond and start at the recent end of the matching key range. The plan no longer needs a separate sort. Compare how many rows reach the sorter in the account-only plans with how many the ordered scan emits.

Match the equality predicate and the order
CREATE INDEX orders_order_idx
ON orders (account_id, created_at DESC, id DESC);

Index columns have an order of their own. In this key, all entries for an account form a contiguous range. Within that range, the descending timestamp and ID match the query’s order. A B-tree can scan in either direction, with direction and null ordering considered together. If the query asked for every account in timestamp order, this account-first key would not provide that same global order.

Indexes and ORDER BY · Bitmap scans

Level 04/08Runtime

When the planner guesses wrong

Why can the planner distinguish a popular account from a rare one? ANALYZE gathers statistics PostgreSQL can consult without scanning the table at planning time. For equality against a value on the most-common-values list, it can use that value’s observed frequency. For other values it estimates a share of the remaining mass. Histograms help with ranges, and physical correlation informs the cost of reading heap pages from an index. Estimated cardinality and estimated access cost are related questions.

Independent column statistics leave a gap. In the fixture there are 37,426 pending orders, but only 1,426 belong to account 1. Most small-account orders are pending. If the planner multiplies the fraction of orders from account 1 by the overall pending fraction, it assumes those facts are independent. That gives 180,000 × (144,000 / 180,000) × (37,426 / 180,000) ≈ 29,941 estimated orders. The product puts too many pending orders in the hot account.

A filter that exposes correlated columns
SELECT id FROM orders WHERE account_id = 1 AND state = 'pending';
Shared hits
3051
Shared reads
0
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Seq ScanordersFilter ((account_id = 1) AND (state = 'pending'::text))Rows removed by filter 178574
2994114261
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Async Capable": false,
      "Relation Name": "orders",
      "Alias": "orders",
      "Startup Cost": 0,
      "Total Cost": 5751,
      "Plan Rows": 29941,
      "Plan Width": 4,
      "Actual Rows": 1426,
      "Actual Loops": 1,
      "Filter": "((account_id = 1) AND (state = 'pending'::text))",
      "Rows Removed by Filter": 178574,
      "Shared Hit Blocks": 3051,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning": {
      "Shared Hit Blocks": 143,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 2.764,
    "Triggers": [],
    "Execution Time": 40.945
  }
]
Shared hits
3051
Shared reads
0
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Seq ScanordersFilter ((account_id = 1) AND (state = 'pending'::text))Rows removed by filter 178574
142614261
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Async Capable": false,
      "Relation Name": "orders",
      "Alias": "orders",
      "Startup Cost": 0,
      "Total Cost": 5751,
      "Plan Rows": 1426,
      "Plan Width": 4,
      "Actual Rows": 1426,
      "Actual Loops": 1,
      "Filter": "((account_id = 1) AND (state = 'pending'::text))",
      "Rows Removed by Filter": 178574,
      "Shared Hit Blocks": 3051,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning": {
      "Shared Hit Blocks": 177,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 2.166,
    "Triggers": [],
    "Execution Time": 23.695
  }
]

With the single-column statistics, the root estimates 29,941 rows. The actual result is 1,426. A multivariate most-common-values list lets PostgreSQL account for the observed combination of account and state. The new estimate is 1,426. No query predicate or index changed between the two captures. A better estimate can steer more consequential choices such as join order, even when a query stays with the same scan.

Measure common combinations of values
CREATE STATISTICS orders_account_state (mcv)
ON account_id, state FROM orders;
ANALYZE orders;

Statistics can become stale as data changes. Even fresh statistics are a summary, and raising the statistics target trades more analysis work and storage for detail. Match a fix to the first large error in the plan. A cardinality error from correlation calls for different evidence than accurate estimates accompanied by expensive heap access.

Planner statistics · Row estimation examples

Level 05/08System

Follow a real B-tree entry

The ordered index is a relation made of fixed-size blocks. Its metapage records where to start. Root and internal items steer the scan downward through key ranges. Leaf items contain the actual key entries and references to heap tuples. After the initial descent, a scan can move across leaf pages as it walks the account range.

Here is the captured route for the newest order for account 42, using the covering index introduced in the next levels. bt_metap identifies the root. The capture finds the leaf item whose heap tuple ID matches the chosen order, then follows decoded parent downlinks back to the root. The resulting block numbers and item offsets come from pageinspect. This reconstructs the route from page contents and does not record the sequence or number of buffer accesses during execution.

Root · index level 2

Index block
209
Item offset
4
Live items on page
5
Free bytes on page
8024

Downlink to index block 617

Internal page · index level 1

Index block
617
Item offset
108
Live items on page
205
Free bytes on page
2456

Downlink to index block 721

Leaf page · index level 0

Index block
721
Item offset
57
Live items on page
204
Free bytes on page
812

Heap TID (2952,32)

Hex payload from bt_page_items · item header excluded
2a 00 00 00 00 00 00 00 00 6e 09 9a 6f ea 02 00 78 a8 02 00 dc 37 00 00

Heap slot · orders

Tuple ID
(2952,32)
Order ID
174200
Page size
8192 B
Free gap
852 B

Tuple payload 14,300 cents · account 42

Root block 209, item 4. Downlink to index block 617.

1 / 4
Step through the captured index blocks and the heap location referenced by the leaf. The heap view describes the tuple location, even when an index-only scan can skip the visit.

The root has block number 209 and level 2, with leaves at level zero. Index block numbers and heap block numbers are in separate relations. The final heap tuple identifier (2952,32) names a heap block and an item number within the block. It is an address of a particular version. An update can change it while the application’s id remains the same.

A heap page has a header and an array of item identifiers growing from the front. The tuple bytes occupy space from the other end. The line pointer lets PostgreSQL use a stable item number even if the tuple bytes move within the page during compaction. The free gap here comes from upper − lower in the captured header. It describes the page at capture time and is separate from table-wide free space estimates.

The hex bytes in the leaf view are the payload reported by bt_page_items, which excludes the item header. On this little-endian Linux capture, the 32-bit values decode directly from their offsets. The timestamp is a signed 64-bit microsecond count relative to January 1, 2000 UTC. The four padding bytes align it after the first integer. The included amount appears in the leaf payload, while internal navigation still uses the key columns.

FieldByte offsetsBytesDecoded value
account_id0–32a 00 00 0042
Alignment padding4–700 00 00 00—
created_at8–1500 6e 09 9a 6f ea 02 002026-01-03T00:23:20.000Z
id16–1978 a8 02 00174200
total_cents20–23dc 37 00 0014300
Read the SQL that reconstructs the path
index-path.sql
WITH RECURSIVE
target AS MATERIALIZED (
  SELECT id, account_id, created_at, total_cents, ctid
  FROM orders WHERE account_id = 42
  ORDER BY created_at DESC, id DESC LIMIT 1
),
pages AS MATERIALIZED (
  SELECT s.*
  FROM generate_series(
    1,
    pg_relation_size('orders_cover_idx') / current_setting('block_size')::int - 1
  ) AS b(blk)
  CROSS JOIN LATERAL bt_page_stats('orders_cover_idx', b.blk) AS s
),
leaf AS (
  SELECT p.blkno, i.itemoffset, i.data
  FROM target t, pages p
  CROSS JOIN LATERAL bt_page_items('orders_cover_idx', p.blkno) AS i
  WHERE p.type = 'l' AND i.htid = t.ctid
),
edges AS (
  SELECT p.blkno, i.itemoffset,
    split_part(ltrim(i.ctid::text, '('), ',', 1)::bigint AS child
  FROM pages p
  CROSS JOIN LATERAL bt_page_items('orders_cover_idx', p.blkno) AS i
  WHERE p.type IN ('r', 'i') AND (p.btpo_next = 0 OR i.itemoffset > 1)
),
path AS (
  SELECT blkno, itemoffset, 0 AS level FROM leaf
  UNION ALL
  SELECT e.blkno, e.itemoffset, p.level + 1
  FROM path p JOIN edges e ON e.child = p.blkno
)
SELECT json_build_object(
  'target', (SELECT row_to_json(t) FROM target t),
  'pages', (SELECT json_agg(json_build_object(
    'block', p.blkno, 'slot', p.itemoffset, 'level', p.level,
    'type', s.type, 'liveItems', s.live_items, 'freeBytes', s.free_size
  ) ORDER BY p.level DESC) FROM path p JOIN pages s USING (blkno)),
  'leafBytes', (SELECT data FROM leaf),
  'meta', (SELECT row_to_json(m) FROM bt_metap('orders_cover_idx') m),
  'heapHeader', (SELECT row_to_json(h) FROM target t
    CROSS JOIN LATERAL page_header(get_raw_page(
      'orders', split_part(ltrim(t.ctid::text, '('), ',', 1)::bigint
    )) h)
);

Level 06/08System

One order, two snapshots

Finding the right key is only part of returning a row. PostgreSQL uses multiversion concurrency control, or MVCC. In general an update creates a new tuple version and marks the old one as superseded by the updating transaction. What a reader sees depends on its snapshot and on transaction status. Two readers can agree on the logical order ID while legitimately returning different physical versions.

In the experiment, session A begins at REPEATABLE READ and reads the newest order for account 42. Session B then adds 100 cents and commits. Before A ends its transaction, A reads again and a new statement takes its own snapshot. The table reports the captured row version each query saw.

ReadOrder IDTotal (cents)Heap TIDxminxmax
A · before the update17420014,300(2952,32)7420
A · after B commits17420014,300(2952,32)742753
New snapshot · after B commits17420014,400(2952,60)7530

Look at the second row. Session A still sees the old total and TID after the commit, but the system column xmax now shows the modifying transaction’s ID. Snapshot visibility is a decision made from the version metadata and the snapshot. It does not require each reader to have a private copy of the tuple header. The fresh snapshot can see the version with the new xmin, a different ctid and the higher total.

Under READ COMMITTED, two separate statements in a transaction can take different snapshots and therefore see different committed versions. Under REPEATABLE READ, the old reader in the capture continues to see its earlier snapshot. Vacuum must respect readers that could still see old versions. The capture closes session A before the second vacuum.

The update also changes a value stored in the covering index. That prevents this update from using the heap-only tuple optimization, which can avoid adding index entries for eligible updates to unindexed columns when the page has room. Index design therefore changes write work as well as read work.

Transaction isolation · Heap-only tuples · System columns

Level 07/08System

When an index-only scan touches the heap

The ordered key can supply id and created_at. The query also needs total_cents, so the first ordered index plan visits the heap for that column. Add it as an included payload column and the index can supply the values selected by the query. An included column takes space in leaf tuples. It does not become another search or sort key.

Store the remaining result column in the index
CREATE INDEX orders_cover_idx
ON orders (account_id, created_at DESC, id DESC)
INCLUDE (total_cents);

There is still a visibility question. The index tuple does not carry the heap tuple’s full MVCC metadata. PostgreSQL checks the visibility map, which tracks all-visible heap pages. When the page is known all-visible, the executor can return the index payload without fetching the heap tuple. When the bit is clear, PostgreSQL must visit the heap to decide whether this index entry points to a version the query can see.

3,051 all-visible heap pages · 0 heap fetches in this index-only scan

Shared hits
1
Shared reads
3
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Limit
20201
Index Only Scanorders_cover_idxIndex condition (account_id = 42)Heap fetches 0
30201
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 0.42,
      "Total Cost": 3.44,
      "Plan Rows": 20,
      "Plan Width": 16,
      "Actual Rows": 20,
      "Actual Loops": 1,
      "Shared Hit Blocks": 1,
      "Shared Read Blocks": 3,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Index Only Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "orders_cover_idx",
          "Relation Name": "orders",
          "Alias": "orders",
          "Startup Cost": 0.42,
          "Total Cost": 4.94,
          "Plan Rows": 30,
          "Plan Width": 16,
          "Actual Rows": 20,
          "Actual Loops": 1,
          "Index Cond": "(account_id = 42)",
          "Rows Removed by Index Recheck": 0,
          "Heap Fetches": 0,
          "Shared Hit Blocks": 1,
          "Shared Read Blocks": 3,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        }
      ]
    },
    "Planning": {
      "Shared Hit Blocks": 150,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 6.328,
    "Triggers": [],
    "Execution Time": 0.207
  }
]

3,050 all-visible heap pages · 2 heap fetches in this index-only scan

Shared hits
5
Shared reads
0
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Limit
20201
Index Only Scanorders_cover_idxIndex condition (account_id = 42)Heap fetches 2
30201
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 0.42,
      "Total Cost": 3.44,
      "Plan Rows": 20,
      "Plan Width": 16,
      "Actual Rows": 20,
      "Actual Loops": 1,
      "Shared Hit Blocks": 5,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Index Only Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "orders_cover_idx",
          "Relation Name": "orders",
          "Alias": "orders",
          "Startup Cost": 0.42,
          "Total Cost": 4.94,
          "Plan Rows": 30,
          "Plan Width": 16,
          "Actual Rows": 20,
          "Actual Loops": 1,
          "Index Cond": "(account_id = 42)",
          "Rows Removed by Index Recheck": 0,
          "Heap Fetches": 2,
          "Shared Hit Blocks": 5,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        }
      ]
    },
    "Planning": {
      "Shared Hit Blocks": 180,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 6.213,
    "Triggers": [],
    "Execution Time": 0.137
  }
]

3,050 all-visible heap pages · 2 heap fetches in this index-only scan

Shared hits
5
Shared reads
0
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Limit
20201
Index Only Scanorders_cover_idxIndex condition (account_id = 42)Heap fetches 2
30201
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 0.42,
      "Total Cost": 6.1,
      "Plan Rows": 20,
      "Plan Width": 16,
      "Actual Rows": 20,
      "Actual Loops": 1,
      "Shared Hit Blocks": 5,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Index Only Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "orders_cover_idx",
          "Relation Name": "orders",
          "Alias": "orders",
          "Startup Cost": 0.42,
          "Total Cost": 8.95,
          "Plan Rows": 30,
          "Plan Width": 16,
          "Actual Rows": 20,
          "Actual Loops": 1,
          "Index Cond": "(account_id = 42)",
          "Rows Removed by Index Recheck": 0,
          "Heap Fetches": 2,
          "Shared Hit Blocks": 5,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        }
      ]
    },
    "Planning": {
      "Shared Hit Blocks": 180,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 3.358,
    "Triggers": [],
    "Execution Time": 0.234
  }
]

3,051 all-visible heap pages · 0 heap fetches in this index-only scan

Shared hits
4
Shared reads
0
Shared dirtied
0
Shared written
0
Plan nodeEstimated rowsActual rows / loopLoops
Limit
20201
Index Only Scanorders_cover_idxIndex condition (account_id = 42)Heap fetches 0
30201
Show raw EXPLAIN output
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 0.42,
      "Total Cost": 3.44,
      "Plan Rows": 20,
      "Plan Width": 16,
      "Actual Rows": 20,
      "Actual Loops": 1,
      "Shared Hit Blocks": 4,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Index Only Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "orders_cover_idx",
          "Relation Name": "orders",
          "Alias": "orders",
          "Startup Cost": 0.42,
          "Total Cost": 4.94,
          "Plan Rows": 30,
          "Plan Width": 16,
          "Actual Rows": 20,
          "Actual Loops": 1,
          "Index Cond": "(account_id = 42)",
          "Rows Removed by Index Recheck": 0,
          "Heap Fetches": 0,
          "Shared Hit Blocks": 4,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        }
      ]
    },
    "Planning": {
      "Shared Hit Blocks": 180,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 2.901,
    "Triggers": [],
    "Execution Time": 0.126
  }
]

The same scan node appears in each stage. The update clears all-visible bits on affected pages. The query can encounter both the old and new index entries while finding twenty visible rows. Closing the old reader allows vacuum to remove the obsolete version. The ordinary vacuum removes the old tuple but leaves behind its dead line pointer because it bypasses index cleanup. The verbose log identifies one remaining dead item identifier. A vacuum with INDEX_CLEANUP ON removes the dead index entry and restores the all-visible bit in this experiment. Heap Fetches counts heap checks made by this scan. It is not a count of distinct pages or device reads.

The visibility map and the planner’s estimate of the all-visible fraction are different pieces of state. The executor checks the bit it needs for each candidate TID regardless of what was estimated. A table that gets frequent updates can make a covering index less effective at avoiding heap visits. Inspect the actual heap fetches before judging whether carrying a wide payload is worthwhile.

Index-only scans · Visibility map inspection · VACUUM cleanup options

Compare the verbose VACUUM logs
VACUUM (VERBOSE) orders
INFO:  vacuuming "postgres.public.orders"
INFO:  launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO:  finished vacuuming "postgres.public.orders": index scans: 0
pages: 0 removed, 3051 remain, 2 scanned (0.07% of total)
tuples: 1 removed, 180000 remain, 0 are dead but not yet removable
removable cutoff: 754, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan bypassed: 1 pages from table (0.03% of total) have 1 dead item identifiers
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 94 hits, 0 misses, 0 dirtied
WAL usage: 2 records, 0 full page images, 285 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_16438"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_16438": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 754, which was 0 XIDs old when operation ended
new relfrozenxid: 754, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 28 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM (VERBOSE, INDEX_CLEANUP ON) orders
INFO:  vacuuming "postgres.public.orders"
INFO:  launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO:  finished vacuuming "postgres.public.orders": index scans: 1
pages: 0 removed, 3051 remain, 2 scanned (0.07% of total)
tuples: 0 removed, 180000 remain, 0 are dead but not yet removable
removable cutoff: 754, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (0.03% of total) had 1 dead item identifiers removed
index "orders_pkey": pages: 496 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "orders_account_idx": pages: 157 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "orders_cover_idx": pages: 894 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 37.885 MB/s, avg write rate: 0.000 MB/s
buffer usage: 1496 hits, 154 misses, 0 dirtied
WAL usage: 6 records, 0 full page images, 500 bytes
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.03 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_16438"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_16438": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 754, which was 0 XIDs old when operation ended
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 22 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
Check yourselfWhy can an Index Only Scan report heap fetches even though every requested column is in the index?

Level 08/08Runtime

Read the counters before changing the query

Distinguish rows, blocks and elapsed time

Rows reveal what the executor has filtered, sorted or emitted. Buffer counters reveal visits to shared pages. A shared hit means the requested block was already in PostgreSQL’s buffer cache. A shared read means PostgreSQL loaded a block into it, and the operating system may still have served that read from its cache. Hits and reads are counts of accesses and can include repeated visits to the same block. They do not measure an SSD’s work directly.

Parent plan nodes report buffer usage inclusive of their children, so adding the counters for every node double-counts work. The plan cards use the plan root for execution buffer totals. The separate planning fields in the raw JSON account for planning work such as catalog access. On plans with repeated execution, actual row counts and loop counts also need to be read together. A node returning two rows per loop across a hundred loops has supplied about two hundred rows to its consumers.

Elapsed time depends on more than the plan shape. A warm cache, concurrent queries, parallelism, JIT, storage and the cost of instrumentation can each change it. The fixture issues the captures sequentially and uses temporary storage, so its counters explain logical work and cache state during this run. The reported times are observations from that setup. For a timing comparison, control the workload and cache state and repeat each run.

Reproduce the evidence in the checkout

Run the command from the Undefined Stack checkout with Python 3 and Docker available. The script uses an image digest, loads the seed, records the plans and page path, and coordinates the two snapshots. It stops the disposable database after the capture. Compare scan kinds and row counts first. Physical block placement and timing can vary with the environment.

Capture the evidence against a new disposable database
python3 src/dives/postgresql-query/capture.py > /tmp/postgresql-query-capture.json

The same query gave us three different explanations for work. The planner needed good estimates to choose an access path. The key order determined how early the executor could stop. Visibility determined whether an index entry was enough to return a row. Those three questions are a useful way to read the next plan you encounter.

End of PostgreSQL queries · 8 levels, Idea to System

Where to next

Browse all 39 divesAll topics