Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 39
Covering reads, skipping groups, and early stopping
Distinguish index-only coverage from loose, skip, and distinct scans, then design ordered keys that reduce heap visits and entries examined.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
An index-only scan can return referenced columns from an index, but PostgreSQL must still establish tuple visibility. Coverage and avoiding heap fetches are related but separate conditions.
02
Vacuum marks heap pages all-visible after proving no tuple needs per-row visibility checks. The executor can then trust index entries for those pages without visiting the heap.
03
A loose index scan repeatedly seeks to the next distinct leading-key value instead of reading every entry. Its work approaches the number of groups times tree navigation rather than total leaf entries.
04
A skip scan can use a compound index when the leading key is not constrained by enumerating or jumping across its distinct values. It is attractive only when that prefix has a manageable cardinality.
05
In a distributed database, pushing distinct or loose scanning to tablet servers prevents every duplicate index entry from crossing the network. The SQL result is unchanged while storage reads and RPC payloads fall.
06
A query can cover output yet scan many duplicate entries, or skip groups yet fetch heap tuples. The best design aligns key order, visibility, projection, filtering, and LIMIT so each form of avoided work is measured separately.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
The node name is not enough: Heap Fetches shows how often visibility required a table visit. Repeat after controlled vacuum and after representative writes.
EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, created_at, status FROM event
WHERE tenant_id=$1 ORDER BY created_at DESC LIMIT 20;The visibility map reports all-visible pages that can support heap-free index-only execution. Extension installation should follow normal governance.
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('public.event');Where native skip scan is unavailable, a recursive next-key probe demonstrates the physical idea. It needs careful null handling and is not automatically the best production query.
WITH RECURSIVE keys(k) AS (
(SELECT min(category) FROM item)
UNION ALL
SELECT (SELECT min(category) FROM item WHERE category > keys.k)
FROM keys WHERE k IS NOT NULL
)
SELECT k FROM keys WHERE k IS NOT NULL;A useful distinct-scan test records groups returned, index entries read, heap fetches, storage rows, and RPCs. Runtime alone cannot show where duplicates were eliminated.
EXPLAIN (ANALYZE, BUFFERS, DIST, COSTS OFF)
SELECT DISTINCT category FROM item ORDER BY category;
-- Compare groups returned with index/storage rows read.08
Optional deep dives with the complete experiments and product-version context behind this guide.