When a database server feels slow, the temptation is to add hardware. Sometimes that is right. More often, a small number of queries are responsible for most of the pain, and fixing them costs an afternoon rather than a larger invoice.

Confirm It Is the Database

First make sure the database is the bottleneck. Check CPU, memory, disk wait and network on the server. High load with high disk wait points to storage or to queries reading far more data than they should. Low database load with slow pages points to the application, a remote API or the network. A quick sanity check is to time a few representative queries directly on the server, bypassing the application.

Collect Evidence Before Changing Anything

You need to know which queries are slow and how often they run. The two properties multiply: a modest query running thousands of times a minute can cost more than a rare heavy report.

Both PostgreSQL and MySQL offer ways to capture this:

• Slow query logging. Both engines can log statements that exceed a time threshold. Set a threshold that captures real offenders without flooding the disk, and check each engine’s documentation for current settings.
• Statement statistics. PostgreSQL has an extension that aggregates query statistics by normalized query text, showing total time, call count and mean time. MySQL has the performance schema with similar summaries. These answer “what costs the most in total?” better than a raw log does.
• Live activity views. Looking at currently running queries during a slowdown shows what is stuck right now and whether sessions are waiting on locks.

Rank by total time consumed, then by worst individual latency. Work from the top.

Read the Execution Plan

For a suspect query, ask the database how it intends to run it with EXPLAIN. In PostgreSQL, EXPLAIN ANALYZE actually executes the query and reports real timings and row counts; remember that it runs the statement, so wrap data-changing statements carefully or avoid it on them. MySQL has EXPLAIN and an analyzing variant in recent versions; check your version’s documentation.

What to look for:

• Full table scans on large tables where you expected an index lookup.
• Big gaps between estimated and actual rows, which suggest stale statistics. Refreshing statistics with ANALYZE in PostgreSQL is a cheap first experiment.
• Sorts or temporary tables spilling to disk.
• Nested loops over large row counts, or joins on columns without indexes.
• Functions wrapped around indexed columns in a WHERE clause, which usually prevent the index from being used.

The Usual Fixes, in Order of Risk

1. Refresh statistics. Lowest risk, sometimes surprisingly effective.
2. Add or adjust an index that matches the query’s filter and sort columns. Composite index column order matters: put the columns used for equality filters first, as a general principle.
3. Rewrite the query. Select only the columns needed, avoid fetching thousands of rows to use ten, replace correlated subqueries with joins where it helps, and paginate with a keyed approach rather than large offsets.
4. Fix the pattern in the application. The classic is the N+1 problem: one query to fetch a list, then one query per item. The database sees thousands of small queries that were individually fast. Batch them.
5. Tune configuration such as memory settings, only after the above. Configuration changes affect everything and are easy to get wrong without measurement.
6. Scale up or add read replicas, when the workload is legitimately beyond a single server.

Every index has a cost: writes get slower, and storage grows. Index what queries need, not everything that could be indexed. Test index creation on a copy first, and know that building an index on a large table can lock or load the server; PostgreSQL supports building concurrently, and MySQL has online options in many cases. Check current documentation for your version.

Triage Table

What you see	Probable cause	First move
One query dominates total time	Missing index or poor plan	EXPLAIN, then index or rewrite
Thousands of tiny queries per request	N+1 pattern	Batch in application code
Fast alone, slow at peak	Lock contention or resource saturation	Check waiting sessions and locks
Plan changes suddenly	Stale or skewed statistics	Refresh statistics, review plan
Slow only on large offsets	Offset pagination	Use keyed pagination
Sorts spilling to disk	Insufficient memory for the sort	Add an index to give order, or review memory settings

A Realistic Example

Suppose an order listing page has become slow as data has grown. Statement statistics show one query, filtering orders by customer and status and sorting by date, accounts for most of the total database time. EXPLAIN shows a scan over the whole orders table followed by a sort. The administrator tests a composite index on customer, status and date on a restored copy of the database, confirms the plan switches to an index lookup with no separate sort, and then creates the index on production during a quiet period using the engine’s non-blocking method. They re-check the statistics afterwards, note the improvement against the baseline recorded earlier, and leave a comment in the schema notes explaining why the index exists.

Frequently Asked Questions

How slow is a slow query? It depends on your application. A threshold that captures the worst few percent of queries is a practical start. Adjust it until the log is useful rather than overwhelming.

Should I run EXPLAIN ANALYZE on production? It executes the query, so it is fine for read-only statements you would run anyway, though heavy queries still consume resources. Avoid it on writes unless wrapped so changes are rolled back.

Why is my index not being used? Common reasons: the table is small enough that a scan is cheaper, the query wraps the column in a function, data types do not match, or statistics are stale. The plan will tell you.

Is adding RAM a valid fix? Sometimes, especially if the working set no longer fits in cache. But it is more reliable to first confirm with metrics that I/O from cache misses is the issue.

How do I find N+1 queries? Look for the same normalized query executed a very large number of times, each cheap. Statement statistics sorted by call count reveal them quickly.

Next Step

Turn on statement statistics or the slow query log if it is not already on, let it run through a normal business day, and take the top three queries by total time into EXPLAIN. Record the baseline before you change anything.