Index Scan With Fetch
Index Scan With Fetch is a database query execution strategy where the database engine first performs an index scan to locate rows in a table using an index, then fetches the actual row data from the table's heap or clustered index. This two-step process is common in databases like PostgreSQL and SQL Server when a query uses an index but requires additional columns not covered by the index. It optimizes data retrieval by minimizing I/O operations through efficient index lookups before accessing the full row.
Developers should understand this concept when optimizing database queries, especially in performance-critical applications where query efficiency impacts response times. It's crucial for scenarios involving non-covering indexes, such as SELECT queries that filter on indexed columns but return additional data, helping to diagnose and improve slow queries by analyzing execution plans. Knowledge of this strategy aids in index design decisions, like creating covering indexes to avoid the fetch step and reduce overhead.