PostgreSQL 18: Performance and maintenance

See how a query runs

Run the query and show the real plan, timings and buffer use.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = '[email protected]';

Caution: EXPLAIN ANALYZE really executes the statement. Wrap INSERT, UPDATE or DELETE in a transaction you roll back.

Create an index

Add an index to speed up lookups on a column.

CREATE INDEX idx_users_email ON users (email);

Create an index without blocking writes

Build the index while the table stays writable. Slower, and it cannot run inside a transaction.

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Vacuum and update statistics

Reclaim dead rows and refresh the planner's statistics for a table.

VACUUM (ANALYZE) users;

See running queries

List sessions and what they are executing.

SELECT pid, state, query FROM pg_stat_activity;

Cancel a running query

Ask a backend to stop its current query, using a pid from pg_stat_activity.

SELECT pg_cancel_backend(12345);