ANALYZE和vacuum

1. ANALYZE — collect statistics about a database

# PostgreSQL: Documentation: 15: ANALYZE

30 18 * * 1-5 /home/pg_statistics.sh

[root@iZwz9ahkm8c1vnw9gb7vdoZ ~]# cat /home/pg_statistics.sh
#!/bin/bash
#
# Script:统计信息维护
# Date:2022-08-02
# -d 指定对应的 租户号，比如：tenant_1008408
# -p 指定端口 如15432
#
pg_repack_log=/home/data
# 备份日期
backup_date=`date "+%Y-%m-%d"`
log_file=pg_statistics_${backup_date}.txt
echo `date` >${pg_repack_log}/${log_file}
#根据实际情况替换数据库用户postgres
for d in $(/usr/bin/psql -U postgres -h 127.0.0.1 -d tenant_1011095 -p 15432 -c "select relname From pg_stat_user_tables order by last_analyze desc;" -A -t)
do
echo `date ` >>${pg_repack_log}/${log_file}
`/usr/bin/psql -U postgres -h 127.0.0.1 -d tenant_1011095 -p 15432 -c " analyze ${d} ;" >> ${pg_repack_log}/${log_file} 2>&1`
echo ${d} >>${pg_repack_log}/${log_file}

done

2. 手工在线收集表统计信息

cattle=> vacuum FREEZE ANALYZE verbose t_cattle_ca_cattleinfo;

PostgreSQL统计信息不准导致错误的执行计划 – Oracle and Linux (oracleonlinux.cn)