How to Analyze PostgreSQL Query Cache-Hit Ratio Using pg_stat_statements

How to Analyze PostgreSQL Query Cache-Hit Ratio Using pg_stat_statements

Purpose

Measuring the cache-hit ratio of your PostgreSQL queries is essential for understanding and optimizing how efficiently your database serves data from memory versus disk. This article explains how to use the pg_stat_statements extension to analyze cache-hit ratios, why this metric matters, and how to interpret and act on the results for improved database performance.

Sample Code from Command Line

 1-- PostgreSQL queries cache-hit ratio from pg_stat_statements
 2-- Requires PostgreSQL 9.1+
 3-- Tested on PostgreSQL 9.1+, 10.x, 11.x, 12.x, 13.0
 4
 5-- postgresql.conf needs before start:
 6-- shared_preload_libraries = 'pg_stat_statements'
 7CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 8
 9SELECT
10    calls,
11    rows,
12    shared_blks_hit,
13    shared_blks_read,
14    shared_blks_hit /
15      GREATEST(shared_blks_hit + shared_blks_read, 1)::float AS shared_blks_hit_ratio,
16    local_blks_hit,
17    local_blks_read,
18    local_blks_hit /
19      GREATEST(local_blks_hit + local_blks_read, 1)::float AS local_blks_hit_ratio,
20    query
21FROM
22    pg_stat_statements
23ORDER BY
24    shared_blks_hit_ratio DESC,
25    local_blks_hit_ratio DESC,
26    rows DESC
27LIMIT 100;

Breakdown & Key Points

• pg_stat_statements Extension: This extension must be enabled in postgresql.conf by setting shared_preload_libraries = 'pg_stat_statements' and requires a server restart. It tracks and aggregates execution statistics for all SQL statements executed by the server4.

• Columns Selected:

  • calls: Number of times the query was executed.
  • rows: Total rows processed or returned.
  • shared_blks_hit / shared_blks_read: Number of times data was found in the shared buffer cache vs. read from disk.
  • shared_blks_hit_ratio: Fraction of shared buffer cache hits over total accesses, using GREATEST() to avoid division by zero2.
  • local_blks_hit / local_blks_read: Same as above, but for the local buffer (used in operations like sorts or hash joins).
  • local_blks_hit_ratio: Local buffer cache hit ratio.
  • query: The SQL statement text.

• Ordering: Results are sorted by highest shared and local cache-hit ratios, then by row count.

Insights & Explanations

• What Is Cache-Hit Ratio?
  The cache-hit ratio measures how often queries can read data directly from PostgreSQL’s memory (shared buffers) instead of slower disk storage. A high ratio indicates efficient memory usage and faster query performance235.

• Why Is It Important?
  High cache-hit ratios mean most data requests are served from memory, reducing disk I/O and improving response times. Low ratios may signal insufficient memory allocation, poor indexing, or inefficient queries57.

• How to Interpret Results:

  • OLTP workloads should aim for a cache-hit ratio above 95% (ideally 99%).
  • OLAP workloads may have lower ratios due to larger working sets5.
  • Identify queries with low hit ratios for optimization opportunities.

• How to Improve Cache-Hit Ratios:

  • Increase shared_buffers (typically 25–40% of total RAM)57.
  • Optimize queries and add appropriate indexes to reduce full table scans57.
  • Regularly monitor and vacuum tables to avoid bloat and dead rows7.

Practical Usage

1. Enable pg_stat_statements:
   Edit postgresql.conf to include:

1shared_preload_libraries = 'pg_stat_statements' 

Restart PostgreSQL, then run PSQL command:

1CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 

2. Run the Provided Query:
   Execute the SQL code above to get a detailed breakdown of cache-hit ratios for your queries.
3. Analyze and Optimize:
   Focus on queries with low hit ratios. Investigate their execution plans, indexing, and memory settings.

Helpful References

• PostgreSQL Documentation: pg_stat_statements
• PostgreSQL Documentation: shared_buffers
• Understanding PostgreSQL Cache Hit Ratio - dbdocs.net
• PostgreSQL Performance Tuning: Cache Hit Ratio Analysis
• How to Improve Low Cache Hit Ratio - Stack Overflow

“Cache hit ratio is a crucial metric for evaluating how effectively your database utilizes its memory buffers to serve query results. By identifying queries with low hit ratios, you can pinpoint potential areas for optimization, such as improving indexing strategies or adjusting memory allocation.”2

Conclusion

By monitoring and acting on cache-hit ratios, you can significantly enhance your PostgreSQL database’s responsiveness and efficiency.**