tuning snippets

Postgres Performance Tuning

Tagged database, performance, postgres, tuning  Languages sql

Tuning memory

To tune memory it helps to understand how much data fits into memory:

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

If the cache hit rate is below 99%, try giving PG more memory.

Finding slow queries

Using the slow-query feature is one option. Telling Postgres to store statistics is easier:

CREATE extension pg_stat_statements;

-- View slowest queries
SELECT 
  (total_time / 1000 / 60) as total_minutes, 
  (total_time/calls) as average_time, 
  query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

Finding tables that are not indexed

SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;

Reference: