PostgreSQL Indexing: How to Find and Fix Slow Queries

Indexes are one of the most important tools for improving PostgreSQL query performance. A well-designed index can make a frequently used query much faster, while unnecessary indexes increase storage usage and slow down writes.

The goal is not to add an index to every column. The goal is to create indexes that match the application's actual query patterns.

How a PostgreSQL Index Helps

Without a suitable index, PostgreSQL may need to scan many rows to find matching data.

For example:

SELECT id, status, total
FROM orders
WHERE customer_id = 1001;

If customer_id is frequently used for filtering, an index can help:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The database can then use the index to locate relevant rows instead of scanning the entire table.

Check the Query Plan First

Do not create indexes based only on assumptions.

Use:

EXPLAIN
SELECT id, status, total
FROM orders
WHERE customer_id = 1001;

For actual execution details:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total
FROM orders
WHERE customer_id = 1001;

Important information includes:

Scan type
Estimated rows
Actual rows
Execution time
Buffer activity

This helps determine whether the query is actually using an efficient access path.

Sequential Scan vs Index Scan

A sequential scan reads rows from the table:

Table
 |
 +-- Row 1
 +-- Row 2
 +-- Row 3
 +-- ...

An index provides another path:

Query
 |
 v
Index
 |
 v
Matching rows
 |
 v
Table

However, PostgreSQL may still choose a sequential scan when it estimates that scanning a large portion of the table is cheaper.

An index existing does not guarantee that PostgreSQL will use it.

Index the Columns You Actually Search

Suppose the application frequently executes:

SELECT *
FROM orders
WHERE customer_id = $1;

An index on customer_id is a reasonable candidate:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

But if the application rarely filters by another column, creating an index there may provide little value.

Review real query patterns before adding indexes.

Composite Indexes

Queries often filter by more than one column:

SELECT id, total
FROM orders
WHERE customer_id = $1
  AND status = $2;

A composite index may be useful:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Column order matters.

An index on:

(customer_id, status)

is not equivalent to:

(status, customer_id)

Choose the order based on the application's common filtering and sorting patterns.

Indexes and Sorting

Indexes can also help queries that frequently sort data.

For example:

SELECT id, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

A potentially useful index is:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

Whether PostgreSQL benefits from this index should still be confirmed with EXPLAIN.

Indexes Have a Cost

Indexes are not free.

They consume:

Disk space
Memory
CPU during maintenance
Write time

For an insert:

INSERT INTO orders(customer_id, status, total)
VALUES (1001, 'Pending', 250);

PostgreSQL may need to update multiple indexes associated with the table.

Therefore:

More indexes
    |
    +--> Potentially faster reads
    |
    +--> More write and storage overhead

The correct balance depends on the workload.

Avoid Indexing Every Column

A table such as:

CREATE TABLE orders (
    id BIGINT,
    customer_id BIGINT,
    status TEXT,
    total NUMERIC,
    created_at TIMESTAMP
);

does not automatically need five separate indexes.

Instead, identify queries such as:

WHERE customer_id = ?
WHERE status = ?
ORDER BY created_at

and determine which access patterns actually matter.

Partial Indexes

When queries frequently target a subset of rows, a partial index can reduce index size.

For example:

CREATE INDEX idx_pending_orders
ON orders(customer_id)
WHERE status = 'Pending';

This can be useful when only a small portion of orders are pending.

The query must match the indexed condition closely enough for PostgreSQL to consider the index.

Common Mistakes

Creating Indexes Without Checking Queries

An unused index adds maintenance overhead without providing meaningful benefits.

Ignoring Composite Index Order

The order of columns affects which queries can efficiently use the index.

Assuming Every Index Is Used

PostgreSQL's planner chooses the execution strategy based on cost estimates.

Adding Indexes Instead of Fixing Bad Queries

A poorly designed query may need query-level optimization rather than another index.

Forgetting Write Performance

Every additional index can increase the cost of inserts, updates, and deletes.

Practical Troubleshooting

When a query becomes slow:

Capture the actual query.
Run EXPLAIN (ANALYZE, BUFFERS).
Check the scan type.
Review estimated and actual rows.
Check existing indexes.
Test a possible index.
Run the query again.
Compare the results.

Do not evaluate an index only by whether the query plan changes. Check actual execution time and the workload's overall behavior.

Best Practices

Start with real slow queries.
Use EXPLAIN (ANALYZE, BUFFERS).
Design indexes around common access patterns.
Consider composite indexes for multi-column queries.
Use partial indexes when only a subset of rows matters.
Remove indexes that are demonstrably unnecessary.
Test index changes against realistic data volumes.
Monitor write performance after adding indexes.

Summary

PostgreSQL indexing is about finding the right balance between read performance and write overhead.

Start with the queries that matter, inspect their execution plans, and create indexes based on actual access patterns. Composite and partial indexes can be particularly useful when they match common application queries.

Do not add indexes simply because a column appears important. Measure the query, understand the workload, and verify that the index provides a meaningful improvement.