Introduction to Database Performance and Tuning

Database tuning is a loop, not a list of settings: measure where the time goes, find the few statements responsible for most of it, read their execution plans, change one thing, and measure again. In most systems a small number of queries account for most of the load, and fixing them with an index or a rewrite does more than any configuration change or hardware upgrade.

This page walks through that loop with a worked example on PostgreSQL, shows the equivalent tools in MySQL, SQL Server and Oracle, and then covers the other areas that matter: memory settings, statistics, locking and connections.

Step 1: find the queries that cost the most

Do not start from the query someone complained about. Start from the database's own record of what it has been running. Every major engine keeps one:

Engine	Where to look	What it needs
PostgreSQL	pg_stat_statements view	Extension added to shared_preload_libraries (restart), then CREATE EXTENSION
MySQL	sys.statement_analysis, Performance Schema, slow query log	Performance Schema is on by default; slow log needs slow_query_log = ON
SQL Server	Query Store, sys.dm_exec_query_stats	Query Store is on by default for new databases since SQL Server 2022
Oracle	AWR reports, V$SQL	AWR requires the Diagnostics Pack licence on Enterprise Edition

In PostgreSQL, add pg_stat_statements to shared_preload_libraries in postgresql.conf and restart (managed services expose this as a parameter, and many enable it already). Then:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not mean time. A 5 ms query called two million times a day costs more than a 30-second report that runs once, and it is usually easier to fix.

Step 2: read the execution plan

The plan is the database telling you how it will run the query. The examples use this table with a million rows:

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL,
    total       numeric(10,2) NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total)
SELECT (random() * 50000)::int,
       (ARRAY['new','paid','shipped','cancelled'])[1 + floor(random() * 4)::int],
       now() - random() * interval '365 days',
       round((random() * 500)::numeric, 2)
FROM generate_series(1, 1000000);

ANALYZE orders;

Now the query the application runs on every "my orders" page:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, status, total
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

Simplified, the output has this shape (your timings and row counts will differ):

Limit (actual time=41.9..41.9 rows=20 loops=1)
  ->  Sort (actual time=41.9..41.9 rows=20 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on orders (actual time=0.9..41.8 rows=21 loops=1)
              Filter: (customer_id = 4242)
              Rows Removed by Filter: 999979
Execution Time: 42.0 ms

Read it from the innermost line outwards. Seq Scan with Rows Removed by Filter: 999979 says the database read the whole table to find about twenty rows. That is the problem. PostgreSQL will often show a Parallel Seq Scan under a Gather Merge or Gather node for a table this size; the diagnosis is the same.

EXPLAIN ANALYZE really executes the statement. For an UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK;.

The same step in other engines:

-- MySQL 8.0.18 and later
EXPLAIN ANALYZE SELECT order_id FROM orders WHERE customer_id = 4242;

-- SQL Server: returns the actual plan as XML alongside the results
SET STATISTICS XML ON;

-- Oracle: plan of the last statement run in this session
-- (row counts need the gather_plan_statistics hint or STATISTICS_LEVEL = ALL)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

Step 3: fix it, then measure again

The query filters on customer_id and sorts by created_at. One composite index serves both:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at DESC);

CONCURRENTLY builds the index without blocking writes; it takes longer and cannot run inside a transaction block. Run the EXPLAIN again:

Limit (actual time=0.03..0.06 rows=20 loops=1)
  ->  Index Scan using orders_customer_created_idx on orders (actual time=0.03..0.05 rows=20 loops=1)
        Index Cond: (customer_id = 4242)
Execution Time: 0.08 ms

The sort has gone as well, because the index already returns rows in the requested order.

Rules for indexes that hold across engines:

• Column order matters. Put columns compared with = first, then the column you range-filter or sort on. An index on (created_at, customer_id) would serve this query poorly.
• Indexes are not free. Each one slows every INSERT, UPDATE and DELETE on the table and takes disk and memory. Drop the ones nothing uses; PostgreSQL shows scan counts in pg_stat_user_indexes, SQL Server in sys.dm_db_index_usage_stats.
• To undo: DROP INDEX CONCURRENTLY orders_customer_created_idx;

Query patterns that defeat indexes

Many slow queries have a usable index that the database cannot use because of how the predicate is written:

-- Function on the column: the index on created_at is ignored
SELECT count(*) FROM orders WHERE date(created_at) = '2026-10-01';

-- Rewrite as a range on the bare column
SELECT count(*) FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

The same applies to:

• Leading wildcards. LIKE '%smith' cannot use an ordinary B-tree index; LIKE 'smith%' can.
• Implicit type conversion. Comparing a string column with a number, or mismatched types in a join, can force a conversion on every row.
• SELECT *. It fetches columns you do not use and prevents index-only scans.
• OFFSET pagination. OFFSET 100000 reads and discards 100,000 rows. Page by key instead: WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 20.
• N+1 queries. An application that runs one query for a list and then one more per row shows up in step 1 as a cheap statement with an enormous call count. The fix is a join or an IN list in the application, not an index.

Keep statistics current

The optimizer chooses plans from statistics about how many rows each condition will match. When they are stale, for instance after a bulk load, it guesses wrong and picks a bad plan for a query that used to be fast.

In an EXPLAIN ANALYZE plan, compare estimated rows with actual rows. If they differ by orders of magnitude, refresh the statistics:

ANALYZE orders;                          -- PostgreSQL
ANALYZE TABLE orders;                    -- MySQL
UPDATE STATISTICS dbo.orders;            -- SQL Server
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');   -- Oracle

All four engines refresh statistics automatically in the background by default. Automatic does not mean immediate, so run it by hand after loading or deleting a large share of a table.

Memory and configuration

Configuration matters most when it is badly wrong, and the defaults of open-source engines are deliberately small.

Engine	Setting	Default	Common starting point on a dedicated server
PostgreSQL	shared_buffers	128 MB	About 25% of RAM
PostgreSQL	work_mem	4 MB	Raise cautiously: it applies per sort or hash operation, per query, not per server
MySQL (InnoDB)	innodb_buffer_pool_size	128 MB	50–75% of RAM
SQL Server	max server memory	Effectively unlimited	Leave memory for the operating system and anything else on the host

Change one setting at a time and compare the same measurements before and after. A configuration change you cannot show an effect for should be reverted.

Waiting is not the same as working

A query can be slow because it is blocked, not because it is expensive. Its plan looks fine and it runs instantly when you test it alone.

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       left(query, 60)     AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

Sessions with wait_event_type = 'Lock' are waiting on another transaction. The usual culprit is a session shown as idle in transaction: an application that opened a transaction and never committed. Keep transactions short, and never hold one open across a network call or user input.

Too many connections cause the same symptom from a different direction. Hundreds of active sessions on a server with 8 cores spend their time contending with each other. Use a connection pool (PgBouncer, ProxySQL, or the pool in your application framework) and size it as a small multiple of the CPU core count, not by the number of users.

Common mistakes

• Tuning without a baseline. If you did not record the numbers before, you cannot tell whether a change helped.
• Adding an index for every slow query. Check whether an existing index would work with a rewritten predicate first.
• Testing on a small development database. Plans change with data volume. A query that uses an index on a thousand rows may scan on ten million, and the reverse.
• Buying hardware first. A full table scan on a faster server is still a full table scan.
• Changing many things at once. You will not know which change mattered, or which one made things worse.
• Ignoring writes. Table and index bloat in PostgreSQL, and index fragmentation in SQL Server, grow with update-heavy workloads. Check that autovacuum is keeping up or that index maintenance jobs are running.

Further reading

• PostgreSQL documentation: Using EXPLAIN
• PostgreSQL documentation: pg_stat_statements
• MySQL Reference Manual: Optimization
• SQL Server documentation: Monitor performance by using the Query Store