Technical Architects Zone (TAZ)

PostgreSQL Performance Scripts

Query Performance, Index Usage, Cache Efficiency, Table Activity, pg_stat_statements & Execution Plans

Introduction

PostgreSQL performance troubleshooting requires visibility into query execution, table access patterns, indexes, buffer usage, temporary-file activity, statistics, database workload, and resource-intensive SQL statements.

This page provides practical PostgreSQL DBA scripts for identifying performance problems and collecting evidence before configuration changes or query-tuning decisions are made.

Important: Performance tuning should be evidence-driven. Avoid changing PostgreSQL configuration, dropping indexes, or rewriting SQL solely because one metric appears unusual. Establish a workload baseline and correlate database observations with CPU, memory, storage latency, concurrency, and application behavior.

Contents

1. Sequential vs Index Scans 2. Unused Index Candidates 3. Index Usage Ratio 4. Database Cache Hit Ratio 5. Table Cache Hit Ratio 6. Tables with Dead Tuples 7. Temporary File Usage 8. Largest Tables and Indexes 9. Currently Long-Running Queries 10. Top Queries with pg_stat_statements 11. EXPLAIN Query Plan 12. EXPLAIN ANALYZE with Buffers 13. Check Table Statistics Freshness 14. Important Performance Settings

1. Sequential vs Index Scans

This query provides a high-level view of sequential and index scan activity for user tables.

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Remember: A sequential scan is not automatically inefficient. PostgreSQL may correctly select a sequential scan for small tables or when a query must read a large proportion of the table.

2. Unused Index Candidates

Indexes with no recorded scans may be candidates for investigation because indexes consume storage and add maintenance overhead to INSERT, UPDATE, DELETE, VACUUM, backup, and recovery operations.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(
        pg_relation_size(indexrelid)
    ) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Very important: Do not drop an index simply because idx_scan = 0. Statistics may have been reset recently, the index may support infrequent workloads, or it may enforce a unique or primary-key constraint. Review the complete workload and index definition first.

3. Index Usage Ratio

The following query provides a simple table-level comparison between sequential and index scans.

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    ROUND(
        100.0 * idx_scan /
        NULLIF(seq_scan + idx_scan, 0),
        2
    ) AS index_scan_pct
FROM pg_stat_user_tables
ORDER BY index_scan_pct NULLS FIRST;

Use this as an investigation aid rather than a performance target. Some tables legitimately benefit from sequential scans.

4. Database Cache Hit Ratio

The cache hit ratio compares blocks served from PostgreSQL shared buffers with blocks that PostgreSQL had to request from the operating system.

SELECT
    datname AS database_name,
    blks_read,
    blks_hit,
    ROUND(
        100.0 * blks_hit /
        NULLIF(blks_hit + blks_read, 0),
        2
    ) AS cache_hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY cache_hit_pct NULLS FIRST;

Do not use a single cache-hit percentage as a universal tuning rule. Workload type, database size, operating-system cache, storage latency, scans, reporting workload, and statistics-reset time all influence interpretation.

5. Table Cache Hit Ratio

A table-level cache view can help identify relations generating significant physical block reads.

SELECT
    schemaname,
    relname AS table_name,
    heap_blks_read,
    heap_blks_hit,
    ROUND(
        100.0 * heap_blks_hit /
        NULLIF(heap_blks_hit + heap_blks_read, 0),
        2
    ) AS heap_cache_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;

6. Tables with Dead Tuples

High dead-tuple accumulation can increase table size and contribute to additional I/O. It may indicate that autovacuum requires investigation.

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(
        100.0 * n_dead_tup /
        NULLIF(n_live_tup + n_dead_tup, 0),
        2
    ) AS dead_tuple_pct,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

The tuple counts in PostgreSQL statistics views are estimates. Use them to identify tables requiring further analysis rather than as exact row counts.

7. Temporary File Usage

Large sorts, hashes, and other operations can spill to temporary files when their memory requirements exceed available working memory.

SELECT
    datname AS database_name,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_data_written,
    temp_bytes
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

Caution: Do not immediately increase work_mem globally because temporary files are being created. work_mem can be consumed by multiple operations inside many concurrent sessions, so aggressive settings can create memory pressure.

8. Largest Tables and Indexes

Large relations are often the most important candidates when investigating storage growth, maintenance duration, query I/O, and index overhead.

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(
        pg_relation_size(relid)
    ) AS table_size,
    pg_size_pretty(
        pg_indexes_size(relid)
    ) AS indexes_size,
    pg_size_pretty(
        pg_total_relation_size(relid)
    ) AS total_size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

9. Currently Long-Running Queries

The following example identifies currently active statements that have been running for more than one minute.

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    clock_timestamp() - query_start AS duration,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < clock_timestamp() - interval '1 minute'
  AND pid <> pg_backend_pid()
ORDER BY duration DESC;

A long-running query is not necessarily a slow query. Determine the expected workload, rows processed, blocking state, execution plan, and business purpose before drawing conclusions.

10. Top Queries with pg_stat_statements

The pg_stat_statements extension provides aggregated planning and execution statistics for normalized SQL statements and is one of the most useful PostgreSQL query-performance tools.

Create the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Queries Consuming the Most Total Execution Time

SELECT
    queryid,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_exec_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_exec_ms,
    rows,
    LEFT(query, 500) AS query_text
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Queries with Highest Mean Execution Time

SELECT
    queryid,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS mean_exec_ms,
    ROUND(total_exec_time::numeric, 2) AS total_exec_ms,
    rows,
    LEFT(query, 500) AS query_text
FROM pg_stat_statements
WHERE calls > 0
ORDER BY mean_exec_time DESC
LIMIT 20;

Configuration note: The pg_stat_statements module normally needs to be loaded through shared_preload_libraries before the extension can collect statistics. Enabling or removing it from shared_preload_libraries requires a PostgreSQL restart.

11. EXPLAIN Query Plan

EXPLAIN displays the execution plan selected by the PostgreSQL planner without executing the SQL statement when the ANALYZE option is not used.

EXPLAIN
SELECT
    employee_id,
    employee_name,
    department_id
FROM public.employee
WHERE department_id = 10;

Review the selected scan method, join strategy, estimated rows, costs, sorting, aggregation, and other plan nodes when investigating query behavior.

12. EXPLAIN ANALYZE with Buffers

Adding ANALYZE executes the SQL statement and returns actual execution statistics. BUFFERS adds buffer-usage information that can help identify I/O-heavy plan operations.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    employee_id,
    employee_name,
    department_id
FROM public.employee
WHERE department_id = 10;

Very important: EXPLAIN ANALYZE actually executes the statement. For INSERT, UPDATE, DELETE, MERGE, and other statements with side effects, those effects occur unless you deliberately protect the test.

Testing a Data-Modifying Statement Safely

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE public.employee
SET department_id = 20
WHERE department_id = 10;

ROLLBACK;

13. Check Table Statistics Freshness

PostgreSQL's optimizer relies heavily on statistics. Stale or insufficient statistics can contribute to inaccurate row estimates and inefficient execution plans.

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC;

Refresh Statistics for a Table

ANALYZE VERBOSE public.employee;

14. Important Performance Settings

The following query provides a convenient snapshot of commonly reviewed PostgreSQL performance-related settings.

SELECT
    name,
    setting,
    unit,
    source,
    pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'effective_cache_size',
    'work_mem',
    'maintenance_work_mem',
    'random_page_cost',
    'effective_io_concurrency',
    'default_statistics_target',
    'max_parallel_workers',
    'max_parallel_workers_per_gather',
    'max_worker_processes',
    'jit'
)
ORDER BY name;

Important: Do not copy generic PostgreSQL tuning values directly into production. Memory, storage type, CPU capacity, database size, concurrency, query patterns, connection pooling, PostgreSQL version, and application requirements must all be considered.

Recommended Performance Troubleshooting Workflow

Step	Action	Purpose
1	Confirm the symptom	Separate database performance from application, network, or infrastructure issues
2	Identify expensive SQL	Find queries consuming significant total or average execution time
3	Review execution plans	Understand scans, joins, estimates, sorting, and I/O
4	Review statistics	Identify stale or insufficient planner statistics
5	Review indexes	Find missing, inefficient, duplicate, oversized, or unused candidates
6	Review memory and temp usage	Identify excessive spills or unsafe memory expectations
7	Correlate infrastructure metrics	Check CPU, memory, disk latency, IOPS, throughput, and network
8	Test one change at a time	Make performance improvement measurable and reversible

Production Performance Checklist

• Establish normal workload and performance baselines.
• Identify high-impact SQL using workload statistics.
• Review execution plans before changing indexes or configuration.
• Compare estimated rows with actual rows in execution plans.
• Monitor sequential and index scan patterns.
• Review unused indexes only across a representative workload window.
• Monitor cache behavior and physical I/O.
• Monitor temporary-file generation.
• Review dead tuples and autovacuum effectiveness.
• Monitor table and index growth.
• Ensure optimizer statistics are sufficiently current.
• Correlate PostgreSQL activity with CPU, memory, disk, and network metrics.
• Load-test significant tuning changes before production deployment.
• Record before-and-after performance measurements.
• Maintain a rollback plan for significant configuration or schema changes.

Key Takeaways

• Sequential scans are not automatically performance problems.
• An index showing zero scans should be investigated rather than immediately dropped.
• Cache-hit ratios require workload context and should not be treated as universal targets.
• Temporary file activity can help identify memory-intensive SQL operations.
• Dead tuple accumulation may indicate that VACUUM activity requires investigation.
• pg_stat_statements provides valuable aggregated query-performance information.
• EXPLAIN shows the planner's estimated execution strategy.
• EXPLAIN ANALYZE executes the SQL statement and adds actual execution statistics.
• Accurate planner statistics are critical for good PostgreSQL execution plans.
• PostgreSQL configuration tuning should be workload-specific and evidence-driven.

Conclusion

PostgreSQL performance tuning is not about finding one setting or creating indexes for every slow query. Effective tuning begins with understanding the workload, identifying SQL statements responsible for significant resource consumption, and analyzing why PostgreSQL selected a particular execution strategy.

Views such as pg_stat_activity, pg_stat_database, pg_stat_user_tables, and pg_stat_user_indexes, combined with pg_stat_statements and execution-plan analysis, provide DBAs with the evidence required for structured troubleshooting.

The safest approach is to establish a baseline, identify the real bottleneck, make one measurable change at a time, validate the improvement under realistic workload conditions, and maintain a rollback path for significant changes.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning