调优

并不是每一行都配得上一个索引槽位；部分索引让树尽量薄，正对查询从不关心的那些数据。多数表都有表结构假装看不见的偏斜：九成订单已关闭，几乎所有事件都已处理，标志列几乎处处为假。全量索引老老实实全记下来，每次写入都交维护费，保护的却是只碰一小片热数据的查询。部分索引不再为没人查的行付钱。 这笔交易拿精度换体积。索引更小、扫描更快、维护更便宜——但只有谓词与索引条件对得上的查询才能用它，其余统统回退到别的计划。所以部分索引是访问模式与存储的刻意配对，不是通用加速。只有热子集稳定、各查询对它的定义一致时才值得建。 只索引你要查的数据 部分索引带一个 WHERE 子句，不满足谓词的行根本不会进树。当大多数行很少被读时，树的体积因此更小，真正关心的那些行查找也更快。只给未关闭订单建的索引，可能比全量小一个数量级：更多内容装进缓存，每次查找碰更少的页，冷历史再怎么涨，计划依然走索引。 谓词从查询里挑，别从列统计里挑。正确的条件是每条重要查询本来就在重复的那一句：状态等于未关闭、删除时间为空。如果各查询切表的方式不同，一个部分索引伺候不了所有人——要么主导的那片胜出，要么建两个部分索引，每个都比全量瘦。定谓词之前，先让查询日志投票。 行更少、维护更省 条目越少，INSERT、UPDATE 还有 VACUUM 要碰的结构就越轻，磁盘占用也能肉眼可见地降下来。对写入频繁又大多是噪音的表，收益最明显。不满足谓词的插入整行跳过索引——没有页分裂风险，也不为它写日志；不改变谓词结果的更新同样跳过索引维护。对每秒吞噬几千冷写的表，省下的维护远超查询加速本身。 索引变小还会让清理更快：需要清理的页少了，膨胀无处藏身，表本来就要付的维护窗口跟着变短。上线后盯着它和全量等价物的体积比；部分索引一旦长到接近全量，说明谓词选出的早已不是少数，交易已经到期——删掉它，或重新定义切片。 部分索引还有一个容易漏掉的边界：规划阶段必须能证明查询条件蕴含索引谓词。把值写成预备语句参数，规划器无法对所有可能值提前证明，因此可能完全不用这个索引；不同写法的等价条件也未必能被识别。把热点条件固定成同一段查询构造，给带真实参数的 EXPLAIN 留一份回归样本，才能知道索引是在生产路径上工作，而不是只在建表演示里好看。 让语句与索引对得上 规划器通常只会为与部分索引谓词完全一致、或由其蕴含的查询选用它。所以查询里的过滤条件要照索引的写法写，规划器才能真正用得上。状态等于未关闭能命中条件相同的索引；状态属于两者之一则不行，哪怕人一眼能看出重叠。匹配器认的是逻辑蕴含，不是字面相似，而且设计上偏保守。 这份严格规定了一条纪律：全库统一热切片条件的写法。索引写的是删除时间为空，查询就不许写成删除时间等于空或包一层合并函数还指望走索引。把热条件收敛到一处——视图、作用域、查询构造器的片段——让每条语句继承逐字相同的谓词。执行计划是最终裁判：计划无视索引，说明两边谓词差在匹配器跨不过去的地方。 坏掉的样子 经典故障是谓词漂移：业务把"未关闭"重定义得多了一个状态，应用查询跟着改了，部分索引却悄悄对不上一半负载。不报错——计划一条一条退化成顺序扫描，延迟爬升，背锅的却找不到一次发布。上线时给核心查询加上计划断言，进持续集成或监控，断言计划里出现这个索引名。谓词漂移在发布当天就被抓住，而不是被发现的那个季度。 第二种故障是切太碎：一张表五个部分索引，各伺候一份报表，合计维护费超过当初要替换的全量索引。用索引统计视图审计：几乎零扫描的索引是删除候选，谓词重叠的是合并候选，合成一个更宽的部分索引。 快速验证 验证索引命中与体积收益： CREATE INDEX ON orders (created_at) WHERE status = 'open'; EXPLAIN SELECT * FROM orders WHERE status = 'open' ORDER BY created_at LIMIT 20; SELECT pg_size_pretty(pg_relation_size('orders_created_at_idx')); 计划必须走部分索引的索引扫描，体积应该是全量等价物的一小部分。 ...

繁忙表持续产生旧行版本，维护速度需要与这种产生速度匹配。降低自动回收门槛，可以让表更早具备被处理的资格，但“符合条件”并不等于“已经完成”。工作者仍需轮到这张表、获得所需锁，并在积压继续增长之前完成足够的有效清理。调优应观察这个完整循环，不能把一个参数值当成后台吞吐的保证。 坏掉的样子 普通更新和删除的触发条件，由固定门槛加上表行数估计乘比例因子构成；PostgreSQL 18 在启用上限时，还会应用最大触发门槛。插入触发清理、由事务年龄驱动的冻结，又有各自的运行原因。因此，只看一个死元组比例，会漏掉其他维护需求；每看见一次清理就断言更新删除门槛被触发，也可能误判真正原因。 把门槛调低，不能释放仍被旧快照需要的版本，也不能创造额外的磁盘吞吐。增加工作者或降低成本延迟，还可能加剧与前台查询的资源竞争。让死元组估计在每个瞬间都接近零，既不是通用的现实目标，也不能充分说明业务性能良好。真正需要维持的是可持续的清理能力、可接受的文件增长、请求延迟和维护成本。 快速验证 使用 PostgreSQL 18 可丢弃实例，确认自动回收与统计收集已启用、有可用工作者，并使用正常的一分钟数据库检查间隔。通过一个全新的 psql -X -v ON_ERROR_STOP=1 连接执行下面的脚本。它最多等待一百三十秒，核对真实自动执行；如果超时，应把这次观察记为失败，不能手动清理一次就冒充自动任务已经运行。 CREATE SCHEMA churn_demo; CREATE TABLE churn_demo.jobs (id integer PRIMARY KEY,n integer) WITH (autovacuum_enabled=false, autovacuum_vacuum_threshold=50, autovacuum_vacuum_scale_factor=0.2); INSERT INTO churn_demo.jobs SELECT g,0 FROM generate_series(1,10000) AS g; SELECT pg_stat_force_next_flush(); VACUUM (ANALYZE) churn_demo.jobs; UPDATE churn_demo.jobs SET n=1 WHERE id<=1000; SELECT pg_stat_force_next_flush(); SELECT n_live_tup,n_dead_tup,vacuum_count,autovacuum_count FROM pg_stat_user_tables WHERE relid='churn_demo.jobs'::regclass; SELECT 50+0.2*reltuples AS old_threshold,50+0.01*reltuples AS new_threshold FROM pg_class WHERE oid='churn_demo.jobs'::regclass; ALTER TABLE churn_demo.jobs SET (autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.01); DO $$ DECLARE deadline timestamptz := clock_timestamp()+interval '130 seconds'; BEGIN LOOP PERFORM pg_stat_clear_snapshot(); EXIT WHEN (SELECT autovacuum_count>0 FROM pg_stat_user_tables WHERE relid='churn_demo.jobs'::regclass); IF clock_timestamp()>deadline THEN RAISE EXCEPTION 'no automatic vacuum observed within the experiment budget'; END IF; PERFORM pg_sleep(0.25); END LOOP; END $$; SELECT pg_stat_clear_snapshot(); SELECT n_dead_tup,last_autovacuum IS NOT NULL AS ran,autovacuum_count>0 AS counted FROM pg_stat_user_tables WHERE relid='churn_demo.jobs'::regclass; SELECT count(*),count(*) FILTER (WHERE n=1) FROM churn_demo.jobs; DO $$ BEGIN IF (SELECT count(*) FROM churn_demo.jobs)<>10000 OR (SELECT count(*) FROM churn_demo.jobs WHERE n=1)<>1000 THEN RAISE EXCEPTION 'maintenance changed application data'; END IF; END $$; DROP SCHEMA churn_demo CASCADE; 测试表最初只关闭自己的常规自动回收，让初始化与受控更新先完成。手动清理并分析建立统计，同时清掉初始化插入积压，然后再更新一千行。显式统计刷新调用用于同步这个短实验，避免待发布的写入增量落在手动维护更新估计值之后；它不是建议在线业务频繁调用的调优开关。 ...

一条过去很快的查询，可以在数据、统计和访问计划都没有变化时变慢。另一个事务可能持有它需要的锁，存储缓存可能比上次更冷，应用也可能在语句进入 PostgreSQL 之前就开始等待。重新检查计划很有用，但它应当放在完整的耗时比较中：先区分时间是花在执行工作上，还是花在等待获得执行机会。不能从“现在更慢”直接推导出“数据与统计一定都变了”。 保留可比较的基线 记录语句形状、有代表性的参数值、返回行数、相关会话设置，以及应用是否使用预备语句。不同参数可能命中完全不同规模的数据。手工使用常量的查询，不一定复现预备语句采用的通用计划；不同角色或搜索路径，也可能改变实际对象与权限。因此，拿一条看起来相似的语句作为对照，并不保证比较条件相同。 尽量在变慢正在发生时观察活动。pg_stat_activity 可以显示一个状态为运行中的查询正在等待锁；运行中并不等于持续消耗处理器。pg_blocking_pids 列出当前阻塞这个后端的会话。查看其他角色活动需要适当权限，因此字段不可见不等于没有工作发生。交互观察最好使用新的自动提交查询，而不是在同一个长事务中不断读取已经缓存的监控快照。 坏掉的样子 不加判断就运行 ANALYZE，相当于把每次变慢都当成统计陈旧。它可能增加维护工作，却完全没有解除真正的阻塞。一张只有一行的表，即使新增索引，也不能释放其他事务持有的行锁。准确的行数估计和很小的顺序扫描，可以与很长的应用等待同时存在；这两种观察并不矛盾。 EXPLAIN ANALYZE 会真正执行语句，写入也不例外，而且测量本身同样可能被阻塞。从生产环境复制一条大范围更新，不是无害的检查。应先看等待情况和锁的拥有者，再选择安全夹具或副作用已经明确的语句来测量。看到某个进程编号出现在阻塞列表中，也不能立即终止不熟悉的事务，因为它可能正在完成另一个有效业务操作。 快速验证 打开三个新的 psql 会话，分别记作甲、乙、丙，连接同一个可丢弃的 PostgreSQL 18 数据库并使用相同角色，保留通常的自动提交行为。名为 query_recheck_demo 的模式必须尚不存在。务必区分会话：把所有代码块顺序粘贴到同一个连接，无法证明事务之间的阻塞。 在甲中建立夹具，然后开启事务，修改唯一一条记录但暂不提交。 \set ON_ERROR_STOP on CREATE SCHEMA query_recheck_demo; CREATE TABLE query_recheck_demo.items (id integer PRIMARY KEY,value integer); INSERT INTO query_recheck_demo.items VALUES (1,0); ANALYZE query_recheck_demo.items; BEGIN; UPDATE query_recheck_demo.items SET value=10 WHERE id=1; SELECT pg_backend_pid() AS blocker_pid; 在乙中发起带执行测量的更新。它会等待甲结束事务，或者等到六十秒的语句超时。 SET application_name='xbcatm_recheck_waiter'; SET statement_timeout='60s'; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) UPDATE query_recheck_demo.items SET value=value+1 WHERE id=1; 乙仍在等待且尚未超时时，在丙中执行观察查询。实验专用应用名称用于定位乙，不需要展示整条查询文本。 SELECT pid,state,wait_event_type,wait_event,pg_blocking_pids(pid) AS blockers FROM pg_stat_activity WHERE application_name='xbcatm_recheck_waiter'; 回到甲，撤销尚未提交的变更并释放锁，然后收集乙执行完毕后的计划。 ROLLBACK; 确认乙已经成功结束后，在丙中检查最终数据并清理夹具。 SELECT value FROM query_recheck_demo.items WHERE id=1; DROP SCHEMA query_recheck_demo CASCADE; PostgreSQL 18.6 实测中，乙的状态为运行中，等待类型是 Lock，具体等待事件是 transactionid，阻塞列表包含甲的进程编号。具体事件可能随争用过程变化，核心证据是乙确实被甲阻塞。甲回滚后，乙把原本的零加成一，最后查询返回一，清理语句移除了整个实验模式。 ...

大量删除之后，PostgreSQL 表可能只剩少量记录，却仍保留很大的文件。普通清理让旧空间可以在表内复用，完全清理则把存活内容写入新文件，同时重建索引。更彻底的空间回收，需要另一套锁与容量预算，应作为有计划的维护操作，而不是一看到文件大小偏高就立刻执行的日常反应。 坏掉的样子 重写需要的是 ACCESS EXCLUSIVE 表锁，普通读取和写入都与它冲突。某个查询虽然已经执行完，但事务仍然打开、继续持有表锁，也会挡住重写。把它叫成独占行锁，或者以为只有写请求受影响，会低估实际中断范围。排队等待重写的强锁请求，还可能影响随后进入的其他取锁请求，不能只看当前命令有没有开始复制数据。 替代文件成功建立之前，原文件仍然存在，所以操作需要额外磁盘空间。等文件系统已经写满才着手完全清理，可能反而没有容纳新文件的余量。应提前评估替代堆表与索引、磁盘读写和日志影响，以及能够承受的持锁时长。常规自动回收不会自动替你执行这类完整重写。 快速验证 在同一个 PostgreSQL 18 可丢弃数据库中，开启甲、乙两个全新的 psql -X -v ON_ERROR_STOP=1 会话。清理命令必须在显式事务块之外执行。乙先创建五千行、删除前四千五百行，然后进行普通清理： CREATE SCHEMA compact_demo; CREATE TABLE compact_demo.items (id integer PRIMARY KEY,payload text) WITH (autovacuum_enabled=false); INSERT INTO compact_demo.items SELECT g,repeat(md5(g::text),10) FROM generate_series(1,5000) AS g; CREATE TABLE compact_demo.baseline AS SELECT pg_relation_size('compact_demo.items') AS heap_bytes, pg_relation_filenode('compact_demo.items') AS file_id; DELETE FROM compact_demo.items WHERE id<=4500; VACUUM (ANALYZE) compact_demo.items; SELECT pg_relation_size('compact_demo.items')=heap_bytes AS same_heap_size, pg_relation_filenode('compact_demo.items')=file_id AS same_file FROM compact_demo.baseline; 两个比较都返回真：堆文件大小与文件标识没有改变。存活行位于原文件尾部，末尾不存在可以截断的连续空页，所以普通清理不能通过截断来缩小这个特定文件。这不代表普通清理永远不会缩文件；如果末尾确实存在可释放的空页，就可能归还相应空间。这里要区分样本布局和命令的一般能力。 ...