PostgreSQL Index-Only Scans

Tip: Covering Index

CREATE INDEX idx_posts_category_title ON posts (category_id, title);

If the query only needs category_id and title, PostgreSQL reads only the index.

Gotcha: Visibility Map

Index-only scans require the visibility map to be up-to-date. Run VACUUM regularly.

Tip: Check for Index-Only Scan

EXPLAIN (ANALYZE, BUFFERS) SELECT category_id, title FROM posts WHERE category_id = 1;

Look for "Index Only Scan" in the output.

Gotcha: Include All Columns

If the query needs a column not in the index, PostgreSQL falls back to a regular index scan.

Tip: INCLUDE Clause (PG 11+)

CREATE INDEX idx_posts_category ON posts (category_id) INCLUDE (title, slug);

Non-key columns stored in the index for index-only scans.

Gotcha: Index Size Trade-off

Including more columns makes the index larger. Balance coverage vs storage.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Index-only scans are PostgreSQL's ability to satisfy a query entirely from an index without accessing the table heap. They're the gold standard for query performance. To maximize index-only scans: include frequently queried columns in the index, keep table visibility maps up-to-date (autovacuum helps), and avoid SELECT * queries that force table lookups. I've seen a 20x performance improvement from converting queries to support index-only scans.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)