Speed Up SQL Queries Using Indexes—With Before-and-After Benchmarks

SQL performance tuning is one of the most impactful ways to improve an application’s responsiveness. One of the most powerful tools in your optimization toolbox? Indexes. In this post, we’ll walk through practical examples showing how adding the right indexes in PostgreSQL can drastically boost query performance—complete with before-and-after execution plans and runtime comparisons.

1. Setting the Stage: A Simple Use Case with Poor Performance

Let’s begin with a sample PostgreSQL database containing a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP
);

We’ll insert 1 million rows to simulate a realistic dataset:

INSERT INTO users (username, email, created_at)
SELECT 
    'user' || i,
    'user' || i || '@example.com',
    NOW() - (i || ' minutes')::interval
FROM generate_series(1, 1000000) AS s(i);

Let’s say we want to fetch a user’s record by email:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user999999@example.com';

The query plan would likely show a sequential scan, which scans every row:

Seq Scan on users  (cost=0.00..17432.00 rows=1 width=64)
  Filter: (email = 'user999999@example.com'::text)

Execution time: ~300ms (average varies by setup)

2. Introducing Indexes: Making Email Queries Blazing Fast

To fix this, we add an index on the email column:

CREATE INDEX idx_users_email ON users(email);

Now, re-run the same query with EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user999999@example.com';

The new plan uses an index scan:

Index Scan using idx_users_email on users  (cost=0.00..8.52 rows=1 width=64)
  Index Cond: (email = 'user999999@example.com'::text)

Execution time: ~1ms — a massive improvement!

Why this works: Indexes allow PostgreSQL to jump directly to the relevant row instead of scanning the whole table.

3. Composite Indexes: Optimizing Multi-Column Filters

Let’s say we frequently filter on both username and created_at:

SELECT * FROM users 
WHERE username = 'user42' AND created_at > NOW() - INTERVAL '1 day';

Adding a composite index helps:

CREATE INDEX idx_users_username_created_at 
  ON users(username, created_at);

Why order matters: PostgreSQL uses the leading column(s) of an index. Reversing the columns can make the index ineffective depending on query shape.

After the index is created, the planner avoids slower scans and instead uses efficient starts from the indexed nodes:

Index Scan using idx_users_username_created_at on users  (cost=0.00..10.00 ...)

General rule: Tailor composite indexes to match the WHERE clause order for optimal performance.

4. Partial Indexes: Index Only the Data You Care About

If your queries always target a specific subset of data, you can use partial indexes. For instance, fetching only recent users:

CREATE INDEX idx_recent_users_email 
  ON users(email)
  WHERE created_at > NOW() - INTERVAL '30 days';

Then, this query benefits from it:

SELECT * FROM users 
WHERE email = 'user42@example.com'
AND created_at > NOW() - INTERVAL '30 days';

Benefits: Smaller index size, faster lookups, better cache locality.

5. Covering Indexes: Avoiding Table Access Altogether

PostgreSQL can skip accessing the table heap entirely if all selected columns are in the index:

CREATE INDEX idx_email_only 
  ON users(email) INCLUDE (username);

This makes queries like the following extremely fast:

SELECT email, username FROM users 
WHERE email = 'user42@example.com';

The query planner may use what’s called an Index Only Scan:

Index Only Scan using idx_email_only on users  (cost=0.00...)

Tips: Use INCLUDE for low-cardinality columns or additional fields required in a SELECT clause.

Conclusion: Let the Query Plan Be Your Compass

As we’ve seen, indexing can reduce query times from hundreds of milliseconds to under one. But it’s not a guesswork game—EXPLAIN ANALYZE should be your guide. By carefully analyzing query plans, understanding index types (single-column, composite, partial, covering), and aligning your indexes with access patterns, you can dramatically enhance performance in real-world PostgreSQL applications.

When performance matters—and it always does—understanding your data access patterns and backing them with the right indexes is the key to lightning-fast queries.

Useful links: