【PostgreSQL】记一次少数据量的表（1w内）但查询缓慢的问题

问题

在表数据不多的情况下（1w条以下），简单的对表进行查询，但查询非常的缓慢

1、解决方案：

1、排查初期以为的查询语句的问题，后面直接新建了一张表做替换，问题暂时解决。

2、问题原因：

**1. 经过分析，发现出现这个问题的原因是这个表一直在进行频繁的 
update 操作，频繁的 update 和 delete 操作会导致表内有大量的碎片，
数据其实没有被真正的物理删除，导致查询的高水位线问题。**

3、最终的解决方案：

​ 1. 写定时任务做定期的表磁盘清理

执行步骤：

【注意】，在生产环境下，如果表数据非常大的情况下，不要轻易执行此操作，会导致表的长时间不可用

-- 1、查看表空间大小
select pg_size_pretty(pg_relation_size('表名'));

-- 2、优化表空间
-- 2.1、维护数据库磁盘，释放空间（注意，在生产环境下，
-- 如果表数据非常大的情况下，不要轻易执行此操作，会导致表的长时间不可用）
vacuum FULL 表名;
-- 2.2、重建索引，替换查询效率
REINDEX TABLE 表名;

清理磁盘空间碎片VACUUM

1、查询表占用空间大小

--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));
 
--查出所有表（包含索引）并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20
 
--查出表大小按大小排序并分离data与index
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes

2、VACUUM进行表空间回收

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ 表名 ] 

参数
FULL

选择"完全"清理，这样可以恢复更多的空间，但是花的时间更多并且在表上施加了排它锁。

FREEZE

选择激进的行"冻结"。指定 FREEZE 相当于执行 VACUUM 时将 vacuum_freeze_min_age 参数设为零。FREEZE 选项将在未来的版本中被取消，并且反对使用，你应当使用设置正确的参数值的方法来达到目的。

VERBOSE

为每个表打印一份详细的清理工作报告

ANALYZE

更新用于优化器的统计信息，以决定执行查询的最有效方法。

table

要清理的表的名称(可以有模式修饰)。缺省时是当前数据库中的所有表。

column

要分析的具体的字段名称。缺省是所有字段。

参考文章：https://www.cnblogs.com/VicLiu/p/12011410.html