Top Postgresql queries to checkup on database performance

These are the list of PostgreSQL queries which help me a lot over the time to troubleshoot problems with the database.

1. Find out which queries running currently

SELECT 
  pid, client_addr, query, state 
FROM pg_stat_activity;

2. Table access stat
This query is really helpful when you want to find out which table is most frequent accessed and by which way. Ex: sequence scan or index scan.

SELECT 
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct
FROM pg_stat_user_tables WHERE (idx_scan + seq_scan) > 0
ORDER BY idx_scan_pct;

3. Find out missing indexes
Well, index is one of the most crucial thing which make the database fast or slow. So check for missing indexes are super important. And we can doing that with simple query like below.

SELECT 
  relname, 
  seq_scan-idx_scan AS too_much_seq, 
  case when seq_scan-idx_scan> 0 THEN 'Missing Index?' ELSE 'OK' END,    
  pg_relation_size(relname::regclass) AS rel_size, 
  seq_scan, 
  idx_scan
FROM pg_stat_all_tables
WHERE schemaname='public' AND pg_relation_size(relname::regclass) > 80000 
ORDER BY too_much_seq DESC;

4. Find out unused indexes
Well, indexes are great however over indexing will slow the database down. So how we know that we’re over indexing. Ok, we can use the following query.

SELECT
  relid::regclass AS table,
  indexrelid::regclass AS index,
  pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
  idx_tup_read,
  idx_tup_fetch,
  idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0;

5. Find out duplicate indexes
Over indexing is one of the problem, and duplicate index also another problem. We can find that out with the following query.

SELECT 
  pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
  (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
  (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (SELECT 
        indexrelid::regclass AS idx, 
        (indrelid::text ||E'\n' || indclass::text ||E'\n'|| indkey::text ||E'\n'||
         coalesce(indexprs::text,'') || E'\n' || coalesce(indpred::text,'')) AS KEY
      FROM pg_index) sub
GROUP BY KEY HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;

6. Find out tables size
Checkup on table size is also important so we can know which table is largest and we can take some action such as: archiving or truncating the table.

SELECT 
  relname as "Table",
  pg_size_pretty(pg_relation_size(relid)) As " Table Size",
  pg_size_pretty(pg_total_relation_size(relid) -
  pg_relation_size(relid)) as "Index Size"
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Actually there’re more ways help to monitor the performance of the database. However those queries I find most useful for my case. So hope it’s also helpful to you.

Advertisements
Top Postgresql queries to checkup on database performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s