Slow queries? How to tune SQL with composite indexes

Why do SQL queries slow down as tables grow larger?

When a web application starts, database queries feel instantaneous because the table contains only a few hundred records. In practice, the database engine can load the entire dataset into RAM effortlessly. However, as the application scales and millions of rows accumulate, the database is forced to perform a full table scan, checking every single record stored on disk. This consumes massive amounts of CPU cycles and disk I/O time.

To prevent this performance degradation, we rely on indexes, which function like the index at the back of a textbook: instead of reading every page to find a specific term, the database checks an ordered structure that points directly to the physical address of the record. When filtering by multiple columns simultaneously, a single-column index is no longer enough. This is where composite indexes come in, allowing multiple columns to be indexed together to satisfy complex search criteria efficiently. If you need to investigate underlying hardware storage limits, check out the guide on how to find Linux disk I/O bottlenecks.

How B-Tree composite indexes work in PostgreSQL

PostgreSQL uses the B-Tree structure by default for its indexes, a balanced tree that keeps data sorted and allows for ultra-fast searches, insertions, and deletions. When we create a composite index involving two or more columns, the order in which those columns are declared in the SQL statement completely changes how the database treats them. In practice, the index first sorts records by the initial column provided. If there are ties or duplicate values in that first column, the database then sorts those matching records by the second column, and so on.

This hierarchical sorting rule creates the famous 'prefix rule', which dictates how the PostgreSQL query planner utilizes the index. If you build a composite index with columns (company_id, status, created_at), the database can efficiently use it for queries filtering only by company_id, or by both company_id and status. However, if your query attempts to filter only by status while completely ignoring the first column, the composite index becomes useless for that specific lookup. To understand how message queues and background workers also require fine-tuning for high throughput, review the tips on tuning RabbitMQ prefetch to zero out latency.

The impact of cardinality on column ordering

Cardinality is a statistical concept that measures the uniqueness of data values within a column. High-cardinality columns contain many distinct values, such as social security numbers, email addresses, or primary keys. Conversely, low-cardinality columns contain very few distinct values that repeat frequently, such as order statuses ('active', 'canceled', 'pending') or user genders. Knowing how to position these columns correctly when planning a composite index is the secret weapon senior engineers use to squeeze maximum performance out of the database.

The golden rule in data engineering is to always place high-cardinality columns as far to the left as possible within the composite index definition. When a highly restrictive column sits at the beginning, PostgreSQL can instantly eliminate a massive slice of records at the very first branch of the B-Tree. If you place a low-cardinality column first, the index must traverse thousands of identical branches before managing to filter out the relevant information, severely reducing search efficiency.

Analyzing real execution plans with EXPLAIN ANALYZE

Creating an index and simply hoping the database engine will use it is never enough; measuring the real-world impact using the EXPLAIN ANALYZE command is mandatory. This instruction runs your query in a controlled environment and returns a detailed report showing exactly which steps the database engine followed, how long each phase took, and whether the planned index was actually triggered. Without this analysis, any optimization is just blind guesswork.

To use this tool in practice, simply prepend EXPLAIN ANALYZE to any SELECT statement. The output will display terms like Index Scan, indicating successful index usage, or Seq Scan, signaling that the database bypassed the indexes and read the entire table row by row. Below is a practical example of how to structure this check in the PostgreSQL console:

EXPLAIN ANALYZE
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01';

If the execution plan returns an unwanted Seq Scan, it means your query does not align with the column order of the existing index, or the internal database statistics need updating via the ANALYZE command so the planner understands the actual data distribution.

Common pitfalls and mistakes when building composite indexes

A classic mistake made by junior developers is creating indexes for virtually every column in a table, imagining it will deliver infinite speed. In reality, every added index consumes extra disk space and forces PostgreSQL to perform additional work whenever an INSERT, UPDATE, or DELETE command runs. Because the index must remain sorted, table modifications trigger rewrites across the B-Tree structures, increasing write overhead and degrading overall application performance.

Another frequent trap involves wrapping indexed columns in functions or mathematical operators within the WHERE clause. If you have an index on the created_at column, but execute a query filtering by WHERE DATE(created_at) = '2026-01-10', PostgreSQL cannot use the index because the DATE() function alters the raw stored value. The proper workaround is to rewrite the filter using range comparisons, allowing the query engine to leverage the composite index without barriers. To explore these architectural concepts further in production, see our related technical analysis on How to test AMS1117 voltage regulator on board with meter.

Final thoughts on database performance optimization

Optimizing SQL queries with composite indexes and rigorous analysis using EXPLAIN ANALYZE forms the indispensable foundation for building scalable and responsive systems. Understanding cardinality concepts, the rigidity of the prefix rule in B-Tree structures, and the operational trade-offs of redundant indexes empowers software engineers to make well-founded technical decisions, eliminating bottlenecks before they impact end users.

Investing time in upfront planning and continuous monitoring prevents emergency code rewrites during high-traffic events. By aligning application query patterns with the physical data architecture in PostgreSQL, you ensure long-term stability, low resource consumption, and sustainable software growth.