Optimizing SQL Queries: Index Use Cases You Can Actually See

Introduction

Indexes are among the most powerful tools for improving database performance. Yet, many developers don’t fully grasp how much of a difference they can make or when they should be used. In this post, we’ll perform hands-on tests to show measurable performance improvements using indexes. We’ll walk through real query examples with before-and-after execution times so you can see exactly how indexes make queries faster.

1. Setting Up the Sample Database

Let’s start by creating a simple sales database to test query performance. We’ll use a table that stores product orders, simulating a common e-commerce setup. Here’s how we create our test data:

CREATE TABLE orders ( 
  id SERIAL PRIMARY KEY, 
  customer_id INT, 
  product_id INT, 
  order_date DATE, 
  total_amount DECIMAL(10,2)
);

-- Insert 100,000 random orders for testing
INSERT INTO orders (customer_id, product_id, order_date, total_amount)
SELECT (random()*1000)::INT, (random()*500)::INT, 
       NOW() - (random() * 365)::INT * INTERVAL '1 day', 
       (random()*100)::DECIMAL(10,2)
FROM generate_series(1, 100000);

Once we’ve populated the database, we can test how fast queries run before adding any indexes.

2. Measuring Query Performance Before Indexing

Let’s start by selecting all orders for a specific customer. This is a common query that could become slow as data grows.

-- Using EXPLAIN ANALYZE to measure query cost
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

The result might look something like this:

Seq Scan on orders (cost=0.00..2100.00 rows=100 width=52)
Execution time: 60 ms

The database performs a sequential scan, meaning it checks every row. This is fine for small tables but scales poorly when you have hundreds of thousands of records.

3. Adding an Index to Improve Performance

Now let’s create an index on the customer_id field and test the performance again.

CREATE INDEX idx_customer_id ON orders (customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

After creating the index, you might see output like this:

Index Scan using idx_customer_id on orders (cost=0.43..15.00 rows=100 width=52)
Execution time: 2.3 ms

The query now uses an Index Scan instead of a Sequential Scan. The execution time drops from 60 ms to around 2 ms — that’s over 25x faster. This improvement demonstrates how indexes allow the database engine to jump directly to relevant records rather than scanning the entire table.

4. Composite Indexes and Range Queries

Many real-world queries use multiple filters. Let’s look at a query combining customer and date filters.

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE customer_id = 123 AND order_date > NOW() - INTERVAL '30 days';

Without an appropriate composite index, this may still perform slower than expected. We can optimize it with a composite index:

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE customer_id = 123 AND order_date > NOW() - INTERVAL '30 days';

You might now see something like:

Index Scan using idx_customer_date on orders (cost=0.44..10.50 rows=30 width=52)
Execution time: 1.1 ms

The optimizer can now use the index more efficiently because both filter columns are covered in a single index tree structure.

5. Monitoring and Maintenance of Indexes

Indexes are not free — they consume extra storage and can slightly slow down insert or update operations. As a best practice, always measure the trade-offs and monitor index usage using views like pg_stat_user_indexes in PostgreSQL:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS times_used
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

Regularly review which indexes are actually used by queries. Drop or consolidate unused indexes to keep the database lean and efficient.

Conclusion

Indexes can dramatically speed up query performance, as shown by our before-and-after execution results. By understanding how they work and applying them strategically, you can ensure your SQL queries stay fast and scalable. Always remember to analyze, measure, and optimize for the right queries rather than blindly creating indexes for every column.

Useful links: