Database Query Optimization: Find and Fix Your Slowest Queries

Database queries are the most common source of application performance problems. An application that takes 200ms to respond is usually spending 150ms of that waiting for database queries. The good news: database query optimization typically produces the highest performance ROI of any engineering effort. Fixing a single slow query can reduce application latency by 50% and halve database server load simultaneously.

This guide covers the complete workflow: finding which queries are slow, reading query execution plans, building the right indexes, rewriting queries for better performance, and configuring connection pooling. Examples use PostgreSQL as the primary database, with MySQL equivalents noted where they differ.

Why Database Queries Bottleneck Applications

Applications talk to databases constantly. A single page load that appears simple to a user may execute dozens of queries: authentication, authorization checks, loading user preferences, fetching primary data, loading related records, and logging the access. Each query adds latency. Each query competes for database CPU, memory, and I/O.

Three root causes drive most database performance problems:

Missing indexes. A query that requires scanning every row in a table (a sequential scan) takes time proportional to the table size. With a million rows, even a simple query can take seconds. The right index makes the same query take milliseconds by allowing the database to jump directly to the relevant rows.

Bad query structure. N+1 query patterns, unnecessary JOINs, loading more data than needed, and using non-index-friendly filter predicates all cause queries to do far more work than necessary.

Insufficient resources. Sometimes queries are well-written but the database server lacks the CPU, RAM, or I/O capacity to execute them efficiently. Connection pool exhaustion is a related problem that can make a fast query appear slow.

Before adding hardware or scaling vertically, always find and fix query-level problems first. They are cheaper to fix and often eliminate the need for hardware investment.

Finding Slow Queries

PostgreSQL: pg_stat_statements

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for all SQL statements. Enable it by adding it to your postgresql.conf:

-- Add to postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all

-- Then enable in your database:
CREATE EXTENSION pg_stat_statements;

After enabling, this query finds your slowest queries by total execution time:

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
  rows,
  round(rows::numeric / calls, 2) AS avg_rows
FROM pg_stat_statements
WHERE calls > 100  -- Only queries called at least 100 times
ORDER BY total_exec_time DESC
LIMIT 20;

Pay attention to the percentage column - it tells you what fraction of total database time each query is consuming. A single query consuming 40% of database time is your highest priority, regardless of its average execution time.

Also check queries with high average time but low total time - these may be infrequent but critical-path queries (payment processing, login) where individual slowness matters more than aggregate time.

Reset statistics to get a fresh view after changes:

SELECT pg_stat_statements_reset();

MySQL: slow_query_log

MySQL has a built-in slow query log. Enable it in your my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1        # Log queries taking more than 100ms
log_queries_not_using_indexes = 1  # Log queries not using indexes
min_examined_row_limit = 100  # Only log if 100+ rows examined

Analyze the log with pt-query-digest from Percona Toolkit:

pt-query-digest /var/log/mysql/slow.log | head -100

The output ranks queries by total query time, showing call count, average time, worst time, and the normalized query text (with literal values replaced by ?).

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE is the PostgreSQL tool for understanding how the database executes a specific query. It runs the query, collects actual execution statistics, and returns the query plan with timing.

Take this example query:

EXPLAIN ANALYZE
SELECT
  o.id,
  o.created_at,
  o.total_amount,
  u.email,
  u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 100;

Problematic output:

Limit  (cost=15234.12..15234.37 rows=100 width=72) (actual time=8234.123..8234.198 rows=100 loops=1)
  ->  Sort  (cost=15234.12..15309.12 rows=30000 width=72) (actual time=8234.121..8234.153 rows=100 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: quicksort  Memory: 12543kB
        ->  Hash Join  (cost=3456.00..14934.12 rows=30000 width=72) (actual time=1234.456..8192.012 rows=28934 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders  (cost=0.00..11234.12 rows=30000 width=48) (actual time=0.012..7123.456 rows=28934 loops=1)
                    Filter: ((status = 'pending') AND (created_at > (now() - '7 days'::interval)))
                    Rows Removed by Filter: 1471066

What this tells you:

• Seq Scan on orders - The database is reading all 1.5 million rows in the orders table
• Rows Removed by Filter: 1471066 - 98% of rows were read and discarded
• The sort operation used 12MB of memory, indicating it did not spill to disk but is sorting a large result set
• Total actual time: 8.2 seconds for a query that should take milliseconds

The fix: Add a composite index on (status, created_at DESC):

CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders(status, created_at DESC)
WHERE status = 'pending';  -- Partial index for active records only

After the index:

Limit  (actual time=2.234..2.312 rows=100 loops=1)
  ->  Index Scan using idx_orders_status_created_at on orders
        (actual time=0.087..2.156 rows=100 loops=1)
        Index Cond: ((status = 'pending') AND (created_at > (now() - '7 days'::interval)))

The query now takes 2ms instead of 8.2 seconds - a 4,100x improvement.

Key terms to look for in EXPLAIN output:

• Seq Scan - Reading every row. Almost always bad on large tables.
• Index Scan - Good. Using an index to find rows.
• Index Only Scan - Best. Query answered entirely from the index without reading table rows.
• Bitmap Heap Scan - Index used for filtering but table rows still read. Often acceptable.
• Hash Join vs Nested Loop - Hash joins are better for large data sets; nested loops are better for small result sets.
• High Rows Removed by Filter - The filter is not being pushed into an index efficiently.

Index Strategies

Composite Indexes

A composite index covers multiple columns. The order matters: the database can use the index efficiently for queries that filter on the first column, or the first two columns, or all columns - but not queries that filter only on the second or third column.

-- This index supports queries filtering on (user_id), (user_id, status),
-- and (user_id, status, created_at)
-- But NOT queries filtering only on (status) or (created_at)
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

Design composite indexes to match your most common query patterns. If you frequently filter by user_id and then order by created_at, put user_id first and created_at second.

Partial Indexes

A partial index covers only the rows matching a condition. This dramatically reduces index size and maintenance cost when your queries target a subset of rows.

-- Index only active orders, not completed or cancelled ones
-- (Assuming 90% of orders are not in 'pending' status)
CREATE INDEX CONCURRENTLY idx_orders_pending_created
ON orders(created_at DESC)
WHERE status = 'pending';

-- Index only unprocessed jobs
CREATE INDEX CONCURRENTLY idx_jobs_unprocessed
ON jobs(scheduled_at)
WHERE processed_at IS NULL;

Covering Indexes

A covering index includes all columns needed by a query in the index itself. This enables an Index Only Scan, which avoids reading the table heap entirely.

-- Query that reads user_id, email, and name frequently
-- Add INCLUDE to cover the non-filter columns
CREATE INDEX CONCURRENTLY idx_users_status_covering
ON users(status, created_at)
INCLUDE (email, name);

With this index, a query filtering on (status, created_at) and returning (email, name) can be answered entirely from the index.

Query Rewriting Patterns

Fix N+1 Queries with JOINs

The most common query anti-pattern is loading a list of records and then loading related data for each record individually.

Bad (N+1 pattern):

-- First query: load orders
SELECT id, user_id, total_amount FROM orders WHERE status = 'pending';
-- Then for each order (potentially hundreds of times):
SELECT email, name FROM users WHERE id = ?;

Fixed (single JOIN):

SELECT
  o.id,
  o.total_amount,
  u.email,
  u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

The JOIN approach executes one query instead of N+1. For 100 pending orders, the N+1 pattern runs 101 queries. The JOIN runs 1.

Use Keyset Pagination Instead of OFFSET

OFFSET pagination gets slower as the offset increases because the database must scan and discard offset rows before returning results.

Bad (OFFSET pagination):

-- This gets slower with every page
SELECT id, title, created_at FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

Fixed (keyset/cursor pagination):

-- Fast regardless of position - uses the index directly
SELECT id, title, created_at FROM posts
WHERE created_at < '2026-03-15T10:00:00Z'  -- cursor from previous page
ORDER BY created_at DESC
LIMIT 20;

Keyset pagination is always O(log n) because it uses the index to jump directly to the starting position. OFFSET pagination is O(n) because it scans from the beginning.

Use Materialized Views for Complex Aggregations

Reporting queries that aggregate large datasets can be pre-computed and stored as materialized views.

-- Complex aggregation that runs slowly on every request
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total_amount) AS revenue,
  COUNT(*) AS order_count,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at);

CREATE UNIQUE INDEX ON monthly_revenue (month);

-- Refresh periodically (e.g., every hour via pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Querying the materialized view is instant. The aggregation runs once per refresh period instead of on every request.

Connection Pool Tuning with PgBouncer

Even perfectly optimized queries can appear slow if the application cannot acquire a database connection quickly. Every application instance needs connections to the database, and PostgreSQL’s maximum connection limit (default: 100) is easily exhausted by modern containerized applications.

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. Applications connect to PgBouncer, which maintains a smaller pool of actual connections to PostgreSQL and multiplexes application connections across them.

Basic PgBouncer configuration (pgbouncer.ini):

[databases]
production = host=your-postgres-host port=5432 dbname=production_db

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode:
# session - one connection per client session (default, safest)
# transaction - one connection per transaction (recommended for stateless apps)
# statement - one connection per statement (most aggressive, breaks transactions)
pool_mode = transaction

# Maximum connections to PostgreSQL backend
server_pool_size = 25

# Maximum client connections to PgBouncer
max_client_conn = 1000

# Log configuration
log_connections = 0
log_disconnections = 0
stats_period = 60

With this configuration, 1000 application connections share 25 actual PostgreSQL connections. This allows PostgreSQL to dedicate its memory and CPU to query execution rather than connection management.

When to scale the database vs fix queries:

Scale the database (bigger instance, read replicas) when:

• You have exhausted query optimization opportunities
• Write throughput is genuinely exceeding single-instance capacity
• You have validated that the bottleneck is CPU or I/O, not query inefficiency

Fix queries when:

• Any query shows a sequential scan on a table with more than 10,000 rows
• pg_stat_statements shows any single query consuming more than 5% of total DB time
• p99 database latency is more than 3x the p50 latency (indicating outlier queries)
• Connection pool wait time is more than 10% of total query time

Our database performance audit identifies the top 10 query optimizations for your specific database workload and delivers a prioritized remediation plan with implementation guidance.