SQL Covering and Partial Indexes: Advanced Indexing Techniques
Beyond basic B-tree indexes, covering and partial indexes solve specific performance problems. Here is how I use them. Basic indexes cover the columns you filter on. Covering and partial indexes go further. A covering index includes all the columns a query needs, so the database never touches the table. A partial index only indexes rows that match a condition, saving space and write cost. After tuning indexes on dozens of databases, these two techniques are the ones that produced the biggest unexpected gains. Covering Indexes When a query selects columns that are not in the index, the database finds the row in the index, then goes back to the table to fetch the remaining columns. This is called a heap fetch, and it is an extra I/O per row. For a query returning thousands of rows, thousands of heap fetches add up. A covering index includes all the queried columns, eliminating the trip to the table. . Query that needs three columns SELECT customer_id, status, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at DESC; A basic index on customer_id lets the database find matching rows quickly,…