[Solved] Which prerequisite must be met for PostgreSQL to perform an index-only scan on a query? — Ans: The index must contain all columns needed by the query and visibility map must... | PostgreSQL

PostgreSQL - Indexing Strategies

Which prerequisite must be met for PostgreSQL to perform an index-only scan on a query?

AThe query must include a JOIN clause

BThe table must have no more than 1000 rows

CThe index must contain all columns needed by the query and visibility map must be set

DThe index must be a unique index

Step-by-Step Solution

Solution:

Step 1: Identify index-only scan conditions
PostgreSQL requires that all requested columns are in the index and that the visibility map indicates tuples are visible.
Step 2: Eliminate incorrect options
Row count, JOIN presence, or uniqueness are not required conditions.
Final Answer:
The index must contain all columns needed by the query and visibility map must be set -> Option C
Quick Check:
Index covers columns + visibility map set [OK]

Quick Trick: Index-only scans need full column coverage and visibility map [OK]

Common Mistakes:

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Which prerequisite must be met for PostgreSQL to perform an index-only scan on a query?