Covering Indexes With INCLUDE for Spatial Queries

Problem Statement

A station-status endpoint that returns thousands of rows per request often spends more time fetching heap tuples than traversing the index, and the fix belongs to the family of composite spatial index techniques: attaching the projection columns to the index itself with an INCLUDE clause. When a bounding-box query over observations returns station_id and sensor_grade for every point in a map viewport, a plain GiST index locates the candidate rows quickly but then makes PostgreSQL visit the heap once per row to read those two columns. A covering index stores that payload in the index leaves, so the query is answered entirely from the index — an index-only scan with zero heap fetches. This page shows how to build one on a GiST or btree_gist composite, why the visibility map governs whether it actually fires, and how to confirm the heap fetches disappeared.

Why the Naive Approach Fails

A standard GiST index on the geometry column looks complete, and functionally it is — but it is not covering:

-- Locates candidates fast, but every returned column lives in the heap
CREATE INDEX idx_observations_geom ON observations USING gist (geom);

The bounding-box scan finds matching entries in the GiST leaves, but each leaf entry holds only the geometry key and a tuple pointer (ctid). To return station_id and sensor_grade, PostgreSQL must dereference every ctid into the heap. On a viewport that matches 40 000 station points, that is 40 000 random heap page visits stacked on top of the index scan — the dominant cost in the query.

# The symptom: a viewport query whose latency is driven by heap I/O, not the index
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("host=localhost dbname=weather user=app_user", row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT station_id, sensor_grade
            FROM observations
            WHERE geom && ST_SetSRID(ST_MakeEnvelope(%s, %s, %s, %s), 4326);
            """,
            (-105.3, 39.5, -104.6, 40.1),
        )
        rows = cur.fetchall()  # fast index scan, then 40k heap dereferences

EXPLAIN (ANALYZE, BUFFERS) on this query shows an Index Scan node with a large Heap Fetches value and a high shared read count — the tell that the heap, not the tree, is the bottleneck.

Production-Ready Implementation

The INCLUDE clause (available for GiST since PostgreSQL 14) appends non-key payload columns to leaf entries without making them part of the search key. When the query’s projection is fully covered by the key plus the included columns, and the visibility map says the relevant pages are all-visible, PostgreSQL performs an index-only scan and never touches the heap. Below is a complete setup and query module.

-- 1. Covering GiST index: geom is the search key; the scalars are payload.
--    Requires PostgreSQL 14+ for INCLUDE on GiST.
CREATE INDEX CONCURRENTLY idx_observations_geom_cover
ON observations
USING gist (geom)
INCLUDE (station_id, sensor_grade);

-- 2. If the query also filters on a scalar column, make it a composite KEY
--    (needs btree_gist) and keep only pure-projection columns in INCLUDE:
--    CREATE EXTENSION IF NOT EXISTS btree_gist;
--    CREATE INDEX CONCURRENTLY idx_observations_geom_grade_cover
--    ON observations USING gist (geom, sensor_grade) INCLUDE (station_id);

-- 3. Refresh the visibility map so index-only scans are eligible.
VACUUM (ANALYZE) observations;

The distinction in step 2 matters: a column you filter on belongs in the key (which needs btree_gist to live beside geometry in the GiST tree), while a column you only return belongs in INCLUDE. Putting a filter column in INCLUDE will not tighten the scan — the predicate would be applied as a post-index recheck instead of pruning the tree.

"""
covering_spatial_query.py
-------------------------
Drive a covering GiST index so a viewport projection runs index-only.
The SELECT list must contain ONLY key + INCLUDE columns for the heap to be skipped.
Requires: psycopg[binary]>=3.1
"""
from __future__ import annotations

import psycopg
from psycopg.rows import dict_row

DSN = "host=localhost dbname=weather user=app_user"

def stations_in_viewport(
    min_lon: float, min_lat: float, max_lon: float, max_lat: float,
    batch: int = 5000,
):
    """
    Returns only station_id and sensor_grade — both covered by
    idx_observations_geom_cover — so no heap fetch is required per row.
    A server-side cursor streams large viewports without buffering in RAM.
    """
    sql = """
        SELECT station_id, sensor_grade
        FROM observations
        WHERE geom && ST_SetSRID(ST_MakeEnvelope(%s, %s, %s, %s), 4326);
    """
    with psycopg.connect(DSN, row_factory=dict_row) as conn:
        with conn.cursor(name="viewport_stream") as cur:
            cur.itersize = batch
            cur.execute(sql, (min_lon, min_lat, max_lon, max_lat))
            for row in cur:
                yield row

The single rule that makes or breaks the optimisation: the SELECT list must contain nothing outside the key and INCLUDE columns. Add observed_at to the projection and, unless it is also included, PostgreSQL falls straight back to a heap-fetching index scan. Keep the covered query’s projection frozen to the covering set. The broader mechanics of when the planner elects an index-only scan are detailed in Index-Only Scan Strategies.

Configuration and Tuning Knobs

Because index-only scans hinge on visibility, tie vacuum cadence to the table’s write rate. On an observations table that receives steady appends, a tighter scale factor keeps the map fresh:

ALTER TABLE observations SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

After a bulk load, refresh the map explicitly rather than waiting for autovacuum, or the first wave of production queries will still hit the heap:

def refresh_visibility_map(dsn: str = DSN) -> None:
    # VACUUM cannot run inside a transaction block; use autocommit.
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute("VACUUM (ANALYZE) observations;")

Verification Steps

Prove the heap fetches are gone. The decisive metric is Heap Fetches in the index-only scan node:

-- 1. Expect "Index Only Scan using idx_observations_geom_cover" with Heap Fetches: 0
EXPLAIN (ANALYZE, BUFFERS)
SELECT station_id, sensor_grade
FROM observations
WHERE geom && ST_SetSRID(ST_MakeEnvelope(-105.3, 39.5, -104.6, 40.1), 4326);

-- 2. Check what fraction of the table's pages are all-visible.
--    Low all-visible counts explain lingering heap fetches.
SELECT relname,
       pg_relpages(oid) AS total_pages,
       (SELECT count(*) FROM pg_visibility(oid) WHERE all_visible) AS visible_pages
FROM pg_class
WHERE relname = 'observations';

-- 3. Confirm the covering index is being used.
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_observations_geom_cover';

A healthy plan reads Index Only Scan with Heap Fetches: 0 and a much lower shared read count than the plain index scan produced. If Heap Fetches stays high, run VACUUM observations and re-check — the visibility map, not the index, is the culprit.

Gotchas Checklist

• A stray projection column defeats the whole index. The instant the SELECT list references a column outside the key and INCLUDE set, the scan reverts to fetching heap tuples. Audit the exact columns your hot query returns and freeze them against the covering set.

• The visibility map, not the index, decides index-only eligibility. A perfectly built covering index still fetches the heap on any page not marked all-visible. High-churn tables need aggressive vacuuming; expect some residual heap fetches between vacuums.

• Filter columns belong in the key, projection columns in INCLUDE. Putting a WHERE column in INCLUDE applies it as a post-index recheck rather than pruning the tree. Promote filter columns into a btree_gist composite key instead.

• Wide payload negates the benefit. Including large geometry or long text bloats leaf pages until the covering index is no cheaper to scan than the heap it replaces. Restrict INCLUDE to small scalars.

• CREATE INDEX CONCURRENTLY cannot run inside a transaction. If your migration tool wraps DDL in a transaction, the concurrent build fails — disable the DDL transaction for that migration step.

Related Topics

• Composite Spatial Indexes — parent page on multi-column GiST design and btree_gist composites
• Index-Only Scan Strategies — the full treatment of when PostgreSQL elects an index-only scan and how the visibility map controls it
• Advanced GiST Indexing & Optimization — top-level guide to GiST mechanics and the two-phase scan model
• Leveraging Index-Only Scans for Point Data — apply covering indexes specifically to point-geometry workloads
• Composite Indexes for Geometry and Timestamp Columns — combine a covering payload with a temporal key column