尧图网页设计 
本文摘要为慢查询加索引后PostgreSQL 查询耗时反而上升深分页接口响应也拖到秒级。用 EXPLAIN ANALYZE 对比实测行数、缓冲读放大与估算偏差定位三类失效成因。一、问题与结论PostgreSQL 15 独立测试库orders表 100 万行status只有completed、pending两个值70% 是completed。建idx_orders_status后SELECT * FROM orders WHERE status completed的Execution Time不降反升列表接口LIMIT 20 OFFSET 200000翻到深页时响应升到秒级。只看计划形状判断不了快慢两处都要回到实测代价上找原因。结论B-tree 的价值是用少量 I/O 排除大多数行。条件命中 30% 以上行时Index Scan逐行回表读堆页随机 I/O 通常贵过一次顺序扫描。成因有三类低选择性列回表放大、多列索引列序与条件错配、统计滞后导致行数估算偏差。判定只能用EXPLAIN (ANALYZE, BUFFERS)的实测值actual rows与估算行数之差、Buffers的shared read、Sort节点耗时占比。EXPLAIN只有估算证明不了回归。二、排查与选择依据先用对照实验区分「计划变差」与「数据变多」再决定改 SQL、改索引还是补统计不要一上来删索引。取基线SET enable_indexscan off; SET enable_bitmapscan off;跑一次EXPLAIN (ANALYZE, BUFFERS)记下Execution Time即顺序扫描对照组。RESET enable_indexscan; RESET enable_bitmapscan;后重跑比较两次Execution Time与Buffers: shared hit/read索引路径更慢且shared read明显更高就是回表放大。对比rows估算与actual rows实测相差 10 倍以上说明统计滞后先ANALYZE再比。看Sort节点出现Sort Method: external merge Disk或排序耗时占大头通常是列序错配后还要全局排序。替代方案与取舍做法适用条件代价与边界不建索引走Seq Scan条件命中 30% 以上行放弃该列加速可能影响同表其他高选择性查询部分索引WHERE status completed查询固定带该谓词索引变小查询不含谓词则不命中覆盖索引INCLUDE (amount)PG 11过滤列、返回列都少索引变大、写放大上升低选择性下仍要读大量索引页keyset 分页WHERE id $last ORDER BY id LIMIT 20按稳定游标顺序翻页不支持随机跳页游标列需唯一有序BRIN索引数据物理有序如created_at体积小等值查询与频繁更新的表不适用不适用的情况写密集表索引维护与 WAL 写放大会抵消读收益、需要随机跳页的后台导出keyset 改写会改变语义、条件本就命中极少行的查询那正是索引强项。三、关键原理回表Index Scan取到ctid后还要读堆页命中行越多随机访问越多Seq Scan连续读能吃满预读。列序B-tree(a, b)只对a的前缀条件高效WHERE b y要扫完整个索引再回表带ORDER BY还会多一个Sort。统计信息规划器读pg_statistic估算行数批量导入或删除后若 autoanalyze 未触发阈值受autovacuum_analyze_scale_factor等参数影响估算会失真DDL 与大批量写之后手动ANALYZE更可控。OFFSETLIMIT 20 OFFSET 200000先取并丢弃 20 万行代价随偏移量近似线性增长keyset 或延迟关联子查询只在索引上取id再回表 20 行把它降为常数级。四、可运行示例环境PostgreSQL 15独立测试库无其他负载输入为 100 万行orders。脚本覆盖两处回归低选择性列建索引与深分页。-- 1. 建表并造数100 万行status 70% 为 completedDROPTABLEIFEXISTSorders;CREATETABLEorders(idSERIALPRIMARYKEY,customer_idINTNOTNULL,statusVARCHAR(20)NOTNULL,amountDECIMAL(10,2),created_atTIMESTAMP);INSERTINTOorders(customer_id,status,amount,created_at)SELECT(random()*10000)::int,CASEWHENrandom()0.7THENcompletedELSEpendingEND,(random()*1000)::decimal(10,2),now()-(random()*interval365 days)FROMgenerate_series(1,1000000);ANALYZEorders;-- 2. 建索引前基线EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;-- 3. 建索引后重测CREATEINDEXidx_orders_statusONorders(status);ANALYZEorders;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;-- 4. 对照组强制顺序扫描SETenable_indexscanoff;SETenable_bitmapscanoff;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;RESET enable_indexscan;RESET enable_bitmapscan;-- 5. 深分页与两种改写EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersORDERBYidLIMIT20OFFSET200000;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREid200000ORDERBYidLIMIT20;EXPLAIN(ANALYZE,BUFFERS)SELECTo.*FROMordersASoJOIN(SELECTidFROMordersORDERBYidLIMIT20OFFSET200000)AStONo.idt.id;预期输出步骤 3 若选中Index Scan using idx_orders_statusIndex Cond: (status completed)actual rows接近 700000Buffers: shared read接近全表页数且Execution Time高于步骤 2即为回归若规划器仍选Seq Scan按下方失败处理。步骤 4 回到Seq Scan耗时应不高于步骤 3。步骤 5 第一条的Execution Time随OFFSET增大而上升后两条在同一结果集下基本稳定。实际输出把每次Planning Time、Execution Time与Buffers填入下表逐行对比。硬件、shared_buffers、缓存冷热各不相同这里不预设毫秒数。步骤计划Execution Time实测填入建索引前Seq Scan待填建索引后Index Scan/Bitmap Heap Scan待填关闭indexscan对照Seq Scan待填OFFSET 200000Limit 全量扫描待填keyset 改写Index ScanLimit待填失败处理建完索引忘记ANALYZE两次对比统计口径不同结论会失真——每次 DDL 后显式执行ANALYZE orders。表只有几万行时规划器会正确选Seq Scan测不出回归——把数据量放大到百万级或临时SET enable_seqscan off观察索引路径的实际代价。五、验证结果与边界判据同一份数据、同一会话参数下建索引后Execution Time高于建索引前且关闭索引扫描后回落。三类成因的证据特征依次是actual rows占比过高低选择性、Sort节点耗时占比高列序错配可用(status, created_at)索引配只含created_at条件的查询复现、估算行数与actual rows相差 10 倍以上统计滞后。代价与边界每条INSERT/UPDATE都要维护索引页并写 WAL低选择性索引读收益薄写成本是刚性的。INCLUDE覆盖索引省回表但索引体积与维护成本同步上升。CREATE STATISTICSPG 10只解决多列相关性估算解决不了低选择性本身。enable_indexscan off、enable_bitmapscan off只用于诊断不要在生产会话长期设置。各大版本的代价模型与统计采样有差异升级后需重新验证。思考低选择性列的索引对复合查询仍有价值删除前如何用pg_stat_user_indexes判断它确实无人使用keyset 分页放弃随机跳页能力遇到「跳到最后一页」的需求该如何取舍参考资料EXPLAIN / EXPLAIN ANALYZEPostgreSQL 15Indexes 概述PostgreSQL 15Multicolumn IndexesPostgreSQL 15Row Estimation ExamplesPostgreSQL 15Query Planning ConfigurationPostgreSQL 15Autovacuum ConfigurationPostgreSQL 15CREATE STATISTICSPostgreSQL 15 PostgreSQL 索引建了查询反而更慢，EXPLAIN ANALYZE 定位 3 种失效场景
本文摘要为慢查询加索引后PostgreSQL 查询耗时反而上升深分页接口响应也拖到秒级。用 EXPLAIN ANALYZE 对比实测行数、缓冲读放大与估算偏差定位三类失效成因。一、问题与结论PostgreSQL 15 独立测试库orders表 100 万行status只有completed、pending两个值70% 是completed。建idx_orders_status后SELECT * FROM orders WHERE status completed的Execution Time不降反升列表接口LIMIT 20 OFFSET 200000翻到深页时响应升到秒级。只看计划形状判断不了快慢两处都要回到实测代价上找原因。结论B-tree 的价值是用少量 I/O 排除大多数行。条件命中 30% 以上行时Index Scan逐行回表读堆页随机 I/O 通常贵过一次顺序扫描。成因有三类低选择性列回表放大、多列索引列序与条件错配、统计滞后导致行数估算偏差。判定只能用EXPLAIN (ANALYZE, BUFFERS)的实测值actual rows与估算行数之差、Buffers的shared read、Sort节点耗时占比。EXPLAIN只有估算证明不了回归。二、排查与选择依据先用对照实验区分「计划变差」与「数据变多」再决定改 SQL、改索引还是补统计不要一上来删索引。取基线SET enable_indexscan off; SET enable_bitmapscan off;跑一次EXPLAIN (ANALYZE, BUFFERS)记下Execution Time即顺序扫描对照组。RESET enable_indexscan; RESET enable_bitmapscan;后重跑比较两次Execution Time与Buffers: shared hit/read索引路径更慢且shared read明显更高就是回表放大。对比rows估算与actual rows实测相差 10 倍以上说明统计滞后先ANALYZE再比。看Sort节点出现Sort Method: external merge Disk或排序耗时占大头通常是列序错配后还要全局排序。替代方案与取舍做法适用条件代价与边界不建索引走Seq Scan条件命中 30% 以上行放弃该列加速可能影响同表其他高选择性查询部分索引WHERE status completed查询固定带该谓词索引变小查询不含谓词则不命中覆盖索引INCLUDE (amount)PG 11过滤列、返回列都少索引变大、写放大上升低选择性下仍要读大量索引页keyset 分页WHERE id $last ORDER BY id LIMIT 20按稳定游标顺序翻页不支持随机跳页游标列需唯一有序BRIN索引数据物理有序如created_at体积小等值查询与频繁更新的表不适用不适用的情况写密集表索引维护与 WAL 写放大会抵消读收益、需要随机跳页的后台导出keyset 改写会改变语义、条件本就命中极少行的查询那正是索引强项。三、关键原理回表Index Scan取到ctid后还要读堆页命中行越多随机访问越多Seq Scan连续读能吃满预读。列序B-tree(a, b)只对a的前缀条件高效WHERE b y要扫完整个索引再回表带ORDER BY还会多一个Sort。统计信息规划器读pg_statistic估算行数批量导入或删除后若 autoanalyze 未触发阈值受autovacuum_analyze_scale_factor等参数影响估算会失真DDL 与大批量写之后手动ANALYZE更可控。OFFSETLIMIT 20 OFFSET 200000先取并丢弃 20 万行代价随偏移量近似线性增长keyset 或延迟关联子查询只在索引上取id再回表 20 行把它降为常数级。四、可运行示例环境PostgreSQL 15独立测试库无其他负载输入为 100 万行orders。脚本覆盖两处回归低选择性列建索引与深分页。-- 1. 建表并造数100 万行status 70% 为 completedDROPTABLEIFEXISTSorders;CREATETABLEorders(idSERIALPRIMARYKEY,customer_idINTNOTNULL,statusVARCHAR(20)NOTNULL,amountDECIMAL(10,2),created_atTIMESTAMP);INSERTINTOorders(customer_id,status,amount,created_at)SELECT(random()*10000)::int,CASEWHENrandom()0.7THENcompletedELSEpendingEND,(random()*1000)::decimal(10,2),now()-(random()*interval365 days)FROMgenerate_series(1,1000000);ANALYZEorders;-- 2. 建索引前基线EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;-- 3. 建索引后重测CREATEINDEXidx_orders_statusONorders(status);ANALYZEorders;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;-- 4. 对照组强制顺序扫描SETenable_indexscanoff;SETenable_bitmapscanoff;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREstatuscompleted;RESET enable_indexscan;RESET enable_bitmapscan;-- 5. 深分页与两种改写EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersORDERBYidLIMIT20OFFSET200000;EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREid200000ORDERBYidLIMIT20;EXPLAIN(ANALYZE,BUFFERS)SELECTo.*FROMordersASoJOIN(SELECTidFROMordersORDERBYidLIMIT20OFFSET200000)AStONo.idt.id;预期输出步骤 3 若选中Index Scan using idx_orders_statusIndex Cond: (status completed)actual rows接近 700000Buffers: shared read接近全表页数且Execution Time高于步骤 2即为回归若规划器仍选Seq Scan按下方失败处理。步骤 4 回到Seq Scan耗时应不高于步骤 3。步骤 5 第一条的Execution Time随OFFSET增大而上升后两条在同一结果集下基本稳定。实际输出把每次Planning Time、Execution Time与Buffers填入下表逐行对比。硬件、shared_buffers、缓存冷热各不相同这里不预设毫秒数。步骤计划Execution Time实测填入建索引前Seq Scan待填建索引后Index Scan/Bitmap Heap Scan待填关闭indexscan对照Seq Scan待填OFFSET 200000Limit 全量扫描待填keyset 改写Index ScanLimit待填失败处理建完索引忘记ANALYZE两次对比统计口径不同结论会失真——每次 DDL 后显式执行ANALYZE orders。表只有几万行时规划器会正确选Seq Scan测不出回归——把数据量放大到百万级或临时SET enable_seqscan off观察索引路径的实际代价。五、验证结果与边界判据同一份数据、同一会话参数下建索引后Execution Time高于建索引前且关闭索引扫描后回落。三类成因的证据特征依次是actual rows占比过高低选择性、Sort节点耗时占比高列序错配可用(status, created_at)索引配只含created_at条件的查询复现、估算行数与actual rows相差 10 倍以上统计滞后。代价与边界每条INSERT/UPDATE都要维护索引页并写 WAL低选择性索引读收益薄写成本是刚性的。INCLUDE覆盖索引省回表但索引体积与维护成本同步上升。CREATE STATISTICSPG 10只解决多列相关性估算解决不了低选择性本身。enable_indexscan off、enable_bitmapscan off只用于诊断不要在生产会话长期设置。各大版本的代价模型与统计采样有差异升级后需重新验证。思考低选择性列的索引对复合查询仍有价值删除前如何用pg_stat_user_indexes判断它确实无人使用keyset 分页放弃随机跳页能力遇到「跳到最后一页」的需求该如何取舍参考资料EXPLAIN / EXPLAIN ANALYZEPostgreSQL 15Indexes 概述PostgreSQL 15Multicolumn IndexesPostgreSQL 15Row Estimation ExamplesPostgreSQL 15Query Planning ConfigurationPostgreSQL 15Autovacuum ConfigurationPostgreSQL 15CREATE STATISTICSPostgreSQL 15