解決postgresql 數(shù)據(jù)庫 update更新慢的原因

select w1.pid as 等待進程,
w1.mode as 等待鎖模式,
w2.usename as 等待用戶,
w2.query as 等待會話,
b1.pid as 鎖的進程,
b1.mode 鎖的鎖模式,
b2.usename as 鎖的用戶,
b2.query as 鎖的會話,
b2.application_name 鎖的應用,
b2.client_addr 鎖的IP地址,
b2.query_start 鎖的語句執(zhí)行時間
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='62560'

SELECT 

C.relname 對象名稱,
l.locktype 可鎖對象的類型,
l.pid 進程id,
l.MODE 持有的鎖模式,
l.GRANTED 是否已經(jīng)對鎖進行授權,
l.fastpath,
psa.datname 數(shù)據(jù)庫名稱,
psa.usesysid 用戶id,
psa.usename 用戶名稱,
psa.application_name 應用程序名稱,
psa.client_addr 連接的IP地址,
psa.client_port 連接使用的TCP端口號,
psa.backend_start 進程開始時間,
psa.xact_start 事務開始時間,
psa.query_start 事務執(zhí)行此語句時間,
psa.state_change 事務狀態(tài)改變時間,
psa.wait_event_type 等待事件類型,
psa.wait_event 等待事件,
psa.STATE 查詢狀態(tài),

backend_xid 事務是否有寫入操作,
backend_xmin 是否執(zhí)事務快照,

psa.query 執(zhí)行語句,
now( ) - query_start 持續(xù)時間

FROM

pg_locks l
INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid )
-- where l.relation = 'tb_base_apparatus'::regclass

where relkind ='r'
ORDER BY query_start asc

SELECT
 c.relname 表名,
 (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自動分析閾值,
 (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4))*reltuples AS 自動清理閾值,
 reltuples::DECIMAL(19,0) 活元組數(shù),
 n_dead_tup::DECIMAL(19,0) 死元組數(shù)
FROM
 pg_class c 

LEFT JOIN pg_stat_all_tables d

 ON C.relname = d.relname
WHERE
 c.relname LIKE'tb%' AND reltuples > 0
 AND n_dead_tup > (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples;

VACUUM FULL VERBOSE 表名;
VACUUM FULL VERBOSE ANALYZE 表名;