PostgreSQL - Indexing Strategies
You have a query using an index-only scan but notice it falls back to a regular index scan. What is a likely cause?
You have a query using an index-only scan but notice it falls back to a regular index scan. What is a likely cause?
medium📝 Debug Q6 of 15
Step-by-Step Solution
Solution:
Step 1: Understand index-only scan dependency on visibility map
Index-only scans rely on the visibility map to know which pages are all-visible and safe to skip heap access.Step 2: Identify why fallback occurs
If the visibility map is outdated (not updated after recent changes), PostgreSQL cannot trust the index-only scan and falls back to a regular index scan.Final Answer:
The visibility map is not updated for recent table changes -> Option AQuick Check:
Visibility map outdated causes fallback = B [OK]
Quick Trick: Visibility map must be current for index-only scans [OK]
Common Mistakes:
- Assuming index corruption causes fallback
- Thinking aggregate functions prevent index-only scans
- Believing primary key absence causes fallback
Master "Indexing Strategies" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes