postgresql_explain_tuning_notes.md

Lock hints

This is a very nice reference http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/ https://www.postgresql.org/docs/9.3/static/view-pg-locks.html But in essence you want

select * from pg_stat_activity where query like 'DELETE%'; 
select
   locktype, relation:regclass, page, tuple, mode, transactionid as tid, virtualtransaction as vtid, pid, granted
from pg_catalog.pg_locks 
where pid = 666;

where you got pid from the pg_stat_activity query. Then you can look at the tid in the lock for the non granted lock - and look that up

select * from pg_locks where transactionid = 66666666666;

And find the query using the pid from the above

Changing settings

Ref: https://www.postgresql.org/docs/9.3/static/functions-admin.html

# for example for a sighup'able setting
SELECT set_config('log_statement_stats', 'off', false);
pg_reload_conf()

Check when a table was last vacuumed/analysed

select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

Getting approximate row counts

select relname, reltuples from pg_class order by reltuples desc limit 40;

Playing with settings

These only apply to your current session

show random_page_cost;
set random_page_cost = 0.5;
explain (analyze, costs, buffers, timing) select blah from blah;

Check hot ratios

SELECT 
  n_tup_upd, n_tup_hot_upd, (n_tup_hot_upd::float / n_tup_upd) as hot_ratio 
FROM  
  pg_stat_user_tables 
WHERE
  relname = 'tablename in current database';

Find unused indexes naive and wrong

This is not actually right - indexes used for unique checks may not show in in idx_scan, idx_tup_read or idx_tup_fetch - use the query in the next section

SELECT 
   schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM
   pg_stat_user_indexes
WHERE
   idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0
ORDER BY
  relname;
   

look for unused indexes

mostly from http://xzilla.net/blog/2008/Jul/Index-pruning-techniques.html

The only change I made was to make it sortable - the trick is to ::text the regclass - otherwise the sort is by ID - not the name of the class. Even if you ORDER BY 1.

select 
    indexrelid::regclass::text as index, relid::regclass::text as table 
from 
    pg_stat_user_indexes 
    JOIN pg_index USING (indexrelid) 
where 
    idx_scan = 0 and indisunique is false
order by 
    index;

Look for missing indexes

From http://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis

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;

Other things from http://xzilla.net/blog/2008/Jul/Index-pruning-techniques.html

use this:

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);