Database Indexing medium
Under what condition will the PostgreSQL/MySQL query optimizer ignore an available B-Tree index for a `SELECT` query?
- A When the query returns a large percentage of the table rows (low selectivity), making a Sequential Scan faster than random I/O index seeks.
- B When the table exceeds a specific, internal size threshold (typically more than one million rows) that makes B-Tree traversal inefficient.
- C Whenever the query involves a multi-table JOIN operation that requires the database to synchronize locks across several disparate partitions.
- D If the targeted column is defined as a variable-length `VARCHAR`, as the optimizer prefers numeric identifiers for all index-based operations.
Answer: A. When the query returns a large percentage of the table rows (low selectivity), making a Sequential Scan faster than random I/O index seeks.
Database optimizers perform cost-based analysis. Sequential I/O (reading the whole table linearly) is much faster per page than Random I/O (jumping between the index and the table data). If the optimizer estimates that the query will return a significant portion of the table (often cited as > 15-20%), it will determine that the overhead of 'seek latency' in the index is more expensive than simply reading the entire table from start to finish.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?