解决postgresql 数据库 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 锁的语句执行时间

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 是否已经对锁进行授权,

l.fastpath,

psa.datname 数据库名称,

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 事务执行此语句时间,

psa.state_change 事务状态改变时间,

psa.wait_event_type 等待事件类型,

psa.wait_event 等待事件,

psa.STATE 查询状态,

backend_xid 事务是否有写入操作,

backend_xmin 是否执事务快照,

psa.query 执行语句,

now( ) - query_start 持续时间

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) 活元组数,

  n_dead_tup:: DECIMAL (19,0) 死元组数

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 表名;