Postgresql Performance Tuning

Enable pg_stat_statements extension. Then:

SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Read bottom-up: leaf nodes first (table scans / index scans), then joins, then aggregates. Look for high cost, rows, and actual time.

EXPLAIN: planner's estimate only.
EXPLAIN ANALYZE: actually runs the query and reports real times.
EXPLAIN (BUFFERS, ANALYZE): + buffer hits/misses — tells you I/O vs cache.

When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially.

CREATE INDEX ON t (col); — default is B-tree.

GIN on to_tsvector columns.

GiST or GIN.

CREATE INDEX ON t USING GIN (col jsonb_path_ops);

Postgres treats NULLs as distinct in unique indexes by default — use partial: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL;

VACUUM: reclaim dead tuples, update stats. Autovacuum: background daemon that triggers based on dead-tuple thresholds. Tune autovacuum_vacuum_scale_factor for hot tables.

Dead tuples + free space in a table after many UPDATE/DELETEs that VACUUM hasn't reclaimed; causes larger reads.

pgstattuple extension or query views like pg_stat_user_tables.