PostgreSQL Query Tuning with EXPLAIN ANALYZE and BUFFERS

A database query can be slow on an otherwise idle VPS because it reads far more rows than it returns. Before adding RAM or increasing PostgreSQL memory settings, inspect the work the query actually performs. This walkthrough uses a disposable dataset to connect SQL predicates, index order and execution-plan evidence.

Scope: PostgreSQL 14–18. Run the lab in a separate test database with room for a few hundred thousand rows and an index. It creates only temporary objects in your session. The commands are a reproducible exercise, not a benchmark or a promised speedup.

Know what EXPLAIN will execute

Plain EXPLAIN shows a proposed plan. EXPLAIN ANALYZE runs the statement. Even a SELECT can execute functions with side effects. Do not analyze an unfamiliar production write assuming that the word “explain” makes it safe. TIMING OFF reduces per-node timing instrumentation while retaining execution counts and overall timing. See PostgreSQL’s EXPLAIN reference.

Build a small, repeatable workload

Open psql in your test database and keep the same session for the entire lab. The synthetic orders distribute tenants and dates deterministically so the filter matches a meaningful subset.

set ON_ERROR_STOP on
SET statement_timeout = '30s';
CREATE TEMP TABLE tc_plan_orders (
    id bigint PRIMARY KEY,
    tenant_id integer NOT NULL,
    created_at timestamp NOT NULL,
    amount numeric(10,2) NOT NULL,
    note text NOT NULL
);
INSERT INTO tc_plan_orders
SELECT g,
       g % 100,
       timestamp '2025-01-01' + (g / 100) * interval '1 minute',
       (g % 10000)::numeric / 100,
       repeat('x', 80)
FROM generate_series(1, 300000) AS g;
ANALYZE tc_plan_orders;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, amount
FROM tc_plan_orders
WHERE tenant_id = 42
  AND created_at >= timestamp '2025-01-02'
ORDER BY created_at DESC
LIMIT 50;

If the timeout fires, stop and inspect the test environment rather than blindly removing the guard. Temporary tables are session-local and disappear on disconnect. Their plan buffer accounting uses local rather than ordinary permanent-table shared buffers; that difference is expected.

Read the plan from work performed to result returned

Before the new index, expect the planner to consider scanning and filtering the table, followed by ordering the matches. Do not require a particular printed plan: settings and versions can change the choice. Identify the scan, the filter, the sort and the limit in your actual output.

Compare estimated rows with actual rows at the scan, not only the final LIMIT. Inspect rows removed by the filter. A node with multiple loops reports per-loop averages for actual rows and timing, so loops matter. Buffer counts include child activity and repeated accesses; summing every node double-counts work. PostgreSQL documents these details in Using EXPLAIN.

The important question is not “Did PostgreSQL use an index?” It is “How much work was necessary to deliver these 50 rows?” An index that still visits nearly every row can be less useful than a sequential scan for a broad report.

Create an index that matches filtering and ordering

CREATE INDEX tc_plan_orders_tenant_created_idx
ON tc_plan_orders (tenant_id, created_at DESC);
ANALYZE tc_plan_orders;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, amount
FROM tc_plan_orders
WHERE tenant_id = 42
  AND created_at >= timestamp '2025-01-02'
ORDER BY created_at DESC
LIMIT 50;

The equality condition selects one tenant’s index range; the next key supports the date boundary and requested ordering. This gives the planner a path that can stop after enough qualifying rows instead of sorting all matches. Multicolumn B-tree effectiveness depends strongly on leading-column constraints; see the official multicolumn-index guide.

Check whether the new plan actually chooses that path, whether the explicit sort disappears, and whether fewer buffer accesses are needed. Save both plans. Run each comparison more than once under similar conditions because the first run may warm caches. Do not clear operating-system caches on a production database to manufacture a cold test.

The index does not include every selected column, so heap access is still needed. Adding INCLUDE columns is a separate tradeoff: larger indexes cost space and write maintenance, and an index-only plan’s ability to avoid heap visits also depends on visibility information. Avoid adding every output column automatically.

When estimates are the real problem

A large estimated-versus-actual row mismatch can cause the planner to choose an inappropriate join strategy or access path. First check whether statistics are current and whether the test uses representative parameter values. A query for a tiny tenant may behave differently from the same SQL for your largest tenant.

For correlated columns, consider extended statistics rather than forcing a scan type. For example, in a real orders table where tenant and status are related, a statistics object could look like this:

-- Illustrative production schema: review before running.
CREATE STATISTICS orders_tenant_status_stats (mcv, dependencies)
ON tenant_id, status FROM app.orders;
ANALYZE app.orders;

This example is not part of the temporary-table lab. Statistics are populated by ANALYZE, not merely by creating the object. They help supported estimation cases; they are not a universal fix for all join errors. See CREATE STATISTICS.

Do not solve every sort with more work_mem

If a real query spills a sort to disk, determine whether the sort is necessary, how many rows reach it and how many copies run concurrently. work_mem is a budget for individual operations, not one shared cap for the entire database. Multiple operations, sessions and parallel workers can multiply memory demand. PostgreSQL explains the allocation model in its resource-consumption documentation.

Test a narrowly scoped session or transaction setting before considering a global increase. Keep the existing value, observe concurrent memory use and compare query latency. A faster isolated query that causes the VPS to run out of memory at peak traffic is a regression.

Transfer the finding to production carefully

The lab’s ordinary CREATE INDEX is suitable for its temporary table. On a busy permanent table, plan the locking, disk space and maintenance window separately. A concurrent index build has different operational constraints and failure handling; do not paste the lab’s DDL into production unchanged.

Evaluate the index against reads and writes, realistic tenant sizes and peak concurrency. Keep a verified backup, retain before/after plans, and define what result justifies keeping the index. Once the query does appropriate work, OS pressure measurements can help determine whether CPU, memory or storage is the next constraint.

-- Optional cleanup before disconnecting from the lab session:
DROP TABLE tc_plan_orders;
RESET statement_timeout;