Why Standard Indexes Fail: The Architecture of the Covering Index
Standard indexes improve query performance by creating a B-Tree structure, but they can introduce bottlenecks due to heap fetches when additional data is retrieved from the main table. Covering indexes solve this issue by including all required columns directly in the index, eliminating the need for heap lookups. This results in faster queries, reduced I/O, and better overall database performance.
- ▪Standard indexes use a B-Tree structure with pointers to the main table (heap), which can cause performance bottlenecks due to heap fetches.
- ▪A covering index includes all columns needed by a query, allowing the database to satisfy the query entirely from the index.
- ▪Covering indexes reduce I/O and memory usage by avoiding heap fetches and eliminating the need for runtime sorting.
- ▪In the Spotify 'Recently Played' example, a covering index on (user_id, played_at DESC) INCLUDE (track_id) optimizes the query significantly.
- ▪Using a covering index can prevent full-table scans, heap jumps, and in-memory sorting, leading to faster response times at scale.
Opening excerpt (first ~120 words) tap to expand
try { if(localStorage) { let currentUser = localStorage.getItem('current_user'); if (currentUser) { currentUser = JSON.parse(currentUser); if (currentUser.id === 2923359) { document.getElementById('article-show-container').classList.add('current-user-is-article-author'); } } } } catch (e) { console.error(e); } OPEYEMI OLUWAGBEMIGA Posted on May 2 Why Standard Indexes Fail: The Architecture of the Covering Index #postgres #architecture #database #sql In my last article, I broke down how and why to use indexes wisely for efficient lookups and data retrieval by identifying fields to index and which not to index. Can read that here. But adding a standard index in some cases is half the battle. Adding an index to a column creates a separate, organized B-Tree for that column.
…
Excerpt limited to ~120 words for fair-use compliance. The full article is at DEV.to (Top).
Discussion
0 commentsMore from DEV.to (Top)
