Database Query Performance: Indexing with Composite and Partial Indexes

Introduction 

The difference between a responsive application and a frustrating user experience often comes down to database query performance. While many developers implement basic single-column indexes, two powerful indexing strategies remain underutilized: composite indexes and partial indexes. This article provides an examination of these two techniques that can enhance query performance. All examples will use PostgreSQL syntax, but the concepts apply broadly across SQL databases. 

Index Basics 

Before diving into our specific strategies, let's quickly review what makes indexes effective: 

• Indexes are specialized data structures that improve data retrieval speeds 

• They work like a book's index, providing a map to quickly locate information 

• The fundamental tradeoff: faster reads at the cost of slower writes 

Read on how indexing works here 

Most developers are familiar with single-column indexes, but true optimization often requires more nuanced approaches. 

With this foundation established, let's explore our two indexing approaches. 

Composite Indexes 

Composite indexes index multiple columns together in a specific order. They are particularly powerful for queries that filter or sort by multiple columns. 

CREATE INDEX idx_users_last_first ON users(last_name, first_name); 

Key Considerations for Composite Indexes: 

1. Column Order Matters 

The order of columns in a composite index significantly impacts its effectiveness: 

• Place columns used in equality conditions (WHERE column = value)first 

• Follow with columns used in range conditions (WHERE column > value) 

For example, if your query is: 

SELECT * FROM users WHERE last_name = 'Smith' AND age > 30; 

The optimal index would be: 

CREATE INDEX idx_users_lastname_age ON users(last_name, age); 

This arrangement allows the database to use the index to first find all rows with last_name = 'Smith' (an equality condition), and then filter within that subset for age > 30 (a range condition). 

2. Selectivity Influences Efficiency 

Column selectivity—the percentage of unique values in a column—plays a major role in index effectiveness: 

• Highly selective columns (many unique values) generally should come first 

• Exception: when query patterns consistently filter on less selective columns first 

For example, if filtering by a boolean is_active column followed by user_id (highly selective), the optimal index order depends on your query patterns: 

If you always filter by both columns: (is_active, user_id) 

If you sometimes only filter by user_id:(user_id, is_active) 

3. Covering Indexes 

A covering index contains all columns needed by a query, allowing the database to satisfy the query using only the index without accessing the table data: 

-- Query: SELECT first_name, last_name FROM users WHERE city = 'Chicago'; 

CREATE INDEX idx_users_covering ON users(city, first_name, last_name); 

Benefits include: 

• Eliminated table lookups 

• Reduced I/O operations 

• Faster query execution 

Partial Indexes 

Instead of indexing every row in a table, partial indexes only include entries for rows that match a specified condition. This results in a smaller index that can be more efficient for both lookups and maintenance. 

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; 

Benefits of Partial Indexes: 

1. Reduced index size: Only relevant rows are indexed 

2. Lower maintenance overhead: Fewer entries to update during modifications 

3. Better cache utilization: Higher chance of keeping the index in memory 

The Write Performance Tradeoff 

Earlier, we touched on the key tradeoff of indexing: faster reads come at the cost of slower writes. It's worth digging into this further. Every index you add introduces overhead during write operations. When data changes in an indexed column, the database must update not only the table row but also every associated index. For tables that experience frequent inserts, updates, or deletes, this extra work can significantly impact performance. 

Conclusion 

Composite and partial indexes represent two powerful tools in the database performance toolkit. By implementing these indexing techniques, you can dramatically improve query performance while minimizing overhead. 

Written by Harriet Oteng