Postgres parameters to check performance

Need to check/Set below parameters: 

show shared_preload_libraries;

show debug_print_plan;

show debug_print_parse;

show logging_collector;

show log_statement;

show log_min_duration_statement;

show log_temp_files;

show log_connections;

show log_disconnections;

show log_lock_waits;

show log_line_prefix;

show log_checkpoints;

show log_error_verbosity;

ALTER DATABASE DATABASE_NAME set shared_preload_libraries = pg_stat_statements;

ALTER DATABASE DATABASE_NAME set log_statement = 'all';

ALTER DATABASE DATABASE_NAME set log_min_duration_statement = 0;

ALTER DATABASE DATABASE_NAME set logging_collector = on;

ALTER DATABASE DATABASE_NAME set debug_print_plan = on;

ALTER DATABASE DATABASE_NAME set debug_print_parse = on;

ALTER DATABASE DATABASE_NAME set log_connections = on;

ALTER DATABASE DATABASE_NAME set log_disconnections = on;

ALTER DATABASE DATABASE_NAME set log_lock_waits = on;

ALTER DATABASE DATABASE_NAME set log_temp_files = 0;

ALTER DATABASE DATABASE_NAME set log_line_prefix = '%t:%r:%u@%d:[%p]: db=%d,user=%u,app=%a,client=%h';

ALTER DATABASE DATABASE_NAME set log_checkpoints = on;

ALTER DATABASE DATABASE_NAME set log_error_verbosity = default;

select tc.table_schema, tc.table_name, kc.column_name

from information_schema.table_constraints tc

  join information_schema.key_column_usage kc 

    on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema and kc.constraint_name = tc.constraint_name

where tc.constraint_type = 'PRIMARY KEY'and kc.ordinal_position is not nullorder by tc.table_schema,

         tc.table_name,

         kc.position_in_unique_constraint;

Queries to check Postgres query performance

Some queries to check Postgres Performance

Show sessions older than 5 minutes

===================================

SELECT

pid,

now() - pg_stat_activity.query_start AS duration,

query,

state

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Show mean time per query

========================

select queryid,query as short_query,

round(total_time::numeric,2) as total_time,

calls, round(mean_time::numeric,2) as mean,

round((100 * total_time / sum(total_time::numeric)

OVER ())::numeric, 2) as percentage_overall

from pg_stat_statements order by total_time desc

limit 20;

Sequential scans on tables

===========================

select schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as AVG

from pg_stat_user_tables

where seq_scan > 0

order by seq_tup_read desc;

Fragmentation of tables

=======================

SELECT

relname AS ObjectName

,pg_stat_get_live_tuples(c.oid) AS LiveTuples

,pg_stat_get_dead_tuples(c.oid) AS DeadTuples

FROM pg_class c

where pg_stat_get_live_tuples(c.oid) > 0 or pg_stat_get_dead_tuples(c.oid) > 0

order by pg_stat_get_live_tuples(c.oid),pg_stat_get_dead_tuples(c.oid) desc;

Show status of tables

=====================

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables

where relname='TABLE_NAME';

Check Locks:

============

select * from pg_locks;

Check Waits:

============

select pid, wait_event_type, wait_event from pg_stat_activity where wait_event is not null;

Any blocks or locked queries/sessions:

=======================================

SELECT

pl.pid as blocked_pid

,psa.usename as blocked_user

,pl2.pid as blocking_pid

,psa2.usename as blocking_user

,psa.query as blocked_statement

FROM pg_catalog.pg_locks pl

JOIN pg_catalog.pg_stat_activity psa

ON pl.pid = psa.pid

JOIN pg_catalog.pg_locks pl2

JOIN pg_catalog.pg_stat_activity psa2

ON pl2.pid = psa2.pid

ON pl.transactionid = pl2.transactionid

AND pl.pid != pl2.pid

WHERE NOT pl.granted;

Connections into the DB

==========================

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Connections waiting for a lock

================================

SELECT count(distinct pid) FROM pg_locks WHERE granted = false;