Database Indexing and Query Optimization: A Practical Guide for Production

Start by Finding the Slow Queries

You cannot optimize what you have not measured. Before touching an index, find out which queries actually hurt. In Postgres, enable the pg_stat_statements extension and sort by total time to see where your database spends its effort:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

In MySQL, turn on the slow query log with a threshold like long_query_time = 0.5 and analyze it with pt-query-digest. Focus on the query with the highest total time, which is calls multiplied by mean time. A query that runs 2ms but executes a million times a day often matters more than a 5-second report that runs once. Fix the biggest total-time offenders first.

Read the Execution Plan

Once you have a target query, ask the database how it runs it. Use EXPLAIN ANALYZE in Postgres or EXPLAIN in MySQL. The single most important thing to look for is a sequential scan (Postgres) or full table scan (MySQL, shown as type: ALL) on a large table. That means the database is reading every row to satisfy the query, which is fine for 1,000 rows and catastrophic for 50 million.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 4213 AND status = 'shipped';

Watch three things in the output: the scan type, the estimated versus actual row counts (a large mismatch means stale statistics; run ANALYZE), and where the time actually goes in nested loops or sorts. The plan tells you exactly what to fix.

Choose the Right Index

Index the columns you filter and join on

The query above filters on customer_id and status. A B-tree index on those columns lets the database jump straight to matching rows instead of scanning the table. B-tree indexes are the default and the right choice for equality and range queries, sorting, and joins.

Column order in composite indexes matters

A composite index on (customer_id, status) is not the same as (status, customer_id). The rule: put the most selective column, or the one always present in your WHERE clause, first. An index on (customer_id, status) can serve a query filtering on customer_id alone, but an index on (status, customer_id) cannot efficiently serve a query filtering only on customer_id. Order the columns to match how you query.

Covering indexes avoid the table entirely

If an index contains every column a query needs, the database answers from the index without touching the table. In Postgres use INCLUDE to add non-key columns:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, status) INCLUDE (total, created_at);

Now a query selecting total and created_at for a customer never reads the heap. This turns a two-step lookup into one and can cut query time dramatically for hot paths.

Partial indexes for skewed data

If you constantly query for a small subset, index only that subset. Indexing only unshipped orders keeps the index tiny and fast:

CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

The Mistakes That Kill Query Performance

Functions on indexed columns

Wrapping an indexed column in a function disables the index. WHERE LOWER(email) = 'a@b.com' forces a scan because the index stores the raw value, not the lowercased one. Either store a normalized column or build an expression index on LOWER(email). The same applies to WHERE created_at::date = '2026-07-11': rewrite it as a range on the raw timestamp instead.

Leading wildcards

LIKE '%term' cannot use a standard B-tree index because it does not know the prefix. For full-text search, use a proper full-text index (Postgres tsvector with GIN, or a trigram index for fuzzy matching) rather than pattern matching.

The N+1 query problem

This one lives in the application, not the database. Your ORM loads 100 orders, then fires one more query per order to load its customer: 101 queries where 2 would do. It rarely shows up in single-query analysis because each query is fast; the damage is in the count. Fix it by eager-loading the association (a JOIN or an IN query) so related data comes back in one round trip. Watching your query count per request, not just per-query latency, is how you catch it.

SELECT star on wide tables

Pulling every column ships data you do not use over the wire and defeats covering indexes. Select only the columns you need.

Do Not Over-Index

Every index speeds up reads and slows down writes, because each INSERT, UPDATE, and DELETE must maintain every index on the table. Indexes also consume storage and memory. A table with 15 indexes, half of them never used, pays a write penalty for nothing. Find unused indexes in Postgres:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Drop indexes with zero scans after confirming they are not needed for a rare but critical job. Aim for the minimum set of indexes that covers your real query patterns, not one index per column just in case.

Keep Statistics and Maintenance Healthy

The query planner relies on table statistics to choose good plans. After large data changes, run ANALYZE so estimates stay accurate. In Postgres, ensure autovacuum is tuned for high-write tables so dead rows do not bloat the table and its indexes. A well-indexed query on a bloated table still crawls. Schedule regular index maintenance for tables with heavy update churn.

Database performance work is ongoing: query patterns shift as your product grows, and last quarter's perfect index set no longer fits. Teams without a dedicated DBA often benefit from folding this into broader platform support. A managed DevOps service can own database performance alongside your infrastructure, and a monthly retainer gives you a senior engineer to profile slow queries and tune indexes as load changes.

Get Your Database Running Fast

InstaDevOps puts a senior DevOps engineer on retainer to profile your slow queries, design the right indexes, and keep your production database healthy under growing load. Plans start at $2,999/mo (Startup) and $4,999/mo (Business), with work typically starting within about 48 hours. Book a free 15-minute call to speed up your database.