pg_stats：Postgres 内部统计机制详解

概述本文梳理 PostgreSQL 内部统计的核心原理与pg_stats视图运行机制内容源自 POSETTE 2026 技术分享。文章系统性讲解pg_stats的定义、生成逻辑以及其对 Postgres 查询规划器决策的核心影响帮助读者全面掌握数据库统计数据驱动 SQL 执行计划的底层逻辑。本文以业务常用的customers数据表作为演示案例CREATE TABLE customers ( id bigserial PRIMARY KEY, city text NOT NULL, state text NOT NULL, signup_date date NOT NULL ); -- Insert 1,000,000 rows以下是日常业务中高频使用的筛选查询语句SELECT * FROM customers WHERE state CA;该表已为state和city字段分别创建独立索引。常规认知中数据库会优先走state字段的索引扫描但EXPLAIN ANALYZE的实际执行结果却截然不同QUERY PLAN ----------------------------------------------------------------- Seq Scan on customers (cost0.00..19682.66 rows173829 width26) (actual time0.025..120.574 rows172001 loops1) Filter: (state CA::text) Rows Removed by Filter: 827972 Buffers: shared hit4601 read2582 Planning: Buffers: shared hit139 Planning Time: 0.371 ms Execution Time: 128.136 ms数据表存在有效索引的前提下数据库最终选择了顺序扫描。本文将深入解析这一现象背后的统计机制原理。查询计划由查询规划器生成Postgres 接收 SQL 查询请求后会通过内置查询规划器生成执行计划。规划器不会直接读取、解析数据表原始数据所有执行决策均依托于系统表pg_statistic中存储的数据汇总信息。pg_statistic存储的汇总数据包含规划器判定执行计划所需的各类核心维度信息数据表中各个字段的唯一值数量字段的高频取值及其出现频率字段数值在取值区间内的整体分布规律数据在磁盘上的物理存储顺序与字段逻辑排序顺序的匹配度pg_statistic的原始数据为机器优化格式可读性极差。为方便人工查询与使用Postgres 封装了 pg_stats 视图以标准化、易读的形式展示所有统计信息。ANALYZE统计数据的生成与更新机制pg_statistic中的汇总数据无法自动生成所有统计指标均通过ANALYZE命令生成并刷新。ANALYZE customers;ANALYZE会对目标表执行全表扫描或抽样扫描计算每个字段的多维统计指标并将结果写入pg_statistic系统表。Postgres 的自动真空清理机制会在后台自动执行ANALYZE但大批量数据载入、数据迁移等场景下需手动执行该命令更新实时统计数据。针对customers表执行统计查询查看核心字段指标SELECT attname, n_distinct, null_frac, correlation FROM pg_stats WHERE tablename customers ORDER BY attname; attname | n_distinct | null_frac | correlation --------------------------------------------------- city | 10106 | 0 | 0.0021338463 id | -1 | 0 | 1 signup_date | 1822 | 0 | 1 state | 50 | 0 | 0.06440461 (4 rows)核心指标释义如下n_distinct字段唯一值的估算数量。state字段恰好有 50 个唯一值与美国州的数量匹配city字段存在 10106 个唯一值符合美国城市数据分布特征。主键id取值为-1是字段唯一无重复的固定标识。null_frac字段空值占总行数的比例。示例中所有字段均定义为非空因此该指标全部为0。correlation取值范围为-1至1用于衡量数据磁盘物理存储顺序与字段逻辑排序顺序的匹配程度。取值1代表数据在磁盘上完全有序自增 ID、增量日期字段均符合该特征取值趋近于0代表数据存储顺序相对字段值随机取值趋近于±1时数据库更倾向于走索引扫描取值趋近于0时索引扫描成本更高更易触发顺序扫描。该指标会作为系数参与规划器的成本计算。高频值统计MCVANALYZE会采集字段的高频取值及对应出现频率两类数据分别存储在 pg_stats 的most_common_vals和most_common_freqs平行数组中。以state字段为例查询其高频值分布数据SELECT unnest(most_common_vals::text::text[]) AS state, unnest(most_common_freqs) AS frequency FROM pg_stats WHERE tablename customers AND attname state LIMIT 5; state | frequency -------------------- CA | 0.17403333 TX | 0.1165 NY | 0.08586667 FL | 0.0666 IL | 0.0474 (5 rows)统计结果显示stateCA的数据占比约 17.4%stateTX占比约 11.6%。高频值的频率数据是规划器估算扫描行数、选择执行扫描方式的核心依据。成本机制规划器的计划选择逻辑查询规划器基于成本计算结果筛选最优执行计划影响计划选择的三大核心成本参数如下random_page_cost随机读取磁盘页的成本对应索引扫描回表查询场景seq_page_cost顺序读取磁盘页的成本对应全表顺序扫描场景cpu_tuple_cost处理单行数据的 CPU 计算成本结合pg_statistic生成的行数估算数据上述成本参数共同决定规划器的两类核心执行选择扫描类型顺序扫描Sequential Scan索引扫描Index Scan位图堆扫描Bitmap Heap Scan连接类型嵌套循环连接Nested Loop哈希连接Hash Join合并连接Merge Join规划器会生成多个候选执行计划计算各计划的总成本最终选取成本最低的方案执行。失真或过期的统计数据会导致行数估算错误进而引发执行计划不合理、SQL 性能下降。精准的统计数据是高效查询执行的核心前提。两类取值的查询计划差异解析对比查询 state 字段高频值CA与低频值WY的执行计划EXPLAIN ANALYZE SELECT * FROM customers WHERE state CA; -- Seq Scan on customers (cost0.00..19682.66 rows174029 ...) -- (actual time0.042..50.257 rows172001 loops1) EXPLAIN ANALYZE SELECT * FROM customers WHERE state WY; -- Index Scan using customers_state_idx on customers -- (cost0.42..13116.39 rows4233 ...) -- (actual time0.045..21.238 rows4300 loops1)stateCA的数据占全表约 18%近 18 万行。针对大规模匹配数据索引扫描需要反复执行索引检索与磁盘页读取综合成本远高于全表顺序扫描因此规划器优先选择顺序扫描。stateWY的匹配数据仅约 4000 行数据选择性极低索引扫描的效率优势显著因此规划器触发索引扫描。可通过手动关闭顺序扫描功能验证成本判定逻辑的合理性SET enable_seqscan off; EXPLAIN ANALYZE SELECT * FROM customers WHERE state CA; -- Index Scan using customers_state_idx on customers -- (cost0.42..32172.73 rows170529 width26) -- (actual time0.053..75.656 rows172001 loops1)强制走索引扫描后执行成本从 19682 升至 32172验证了原顺序扫描计划为最优选择。高频值统计数据精准识别了 CA 的高占比特征帮助规划器规避低效的索引扫描充分体现了高频值统计在倾斜数据场景下的核心价值。直方图适配非等值范围查询高频值统计仅适用于等值查询场景。针对时间范围筛选、数值区间对比等范围查询ANALYZE会生成字段直方图统计数据辅助规划器完成计划判定。Postgres 默认生成 100 个等深直方图桶每个桶承载的数据行数基本一致。查询signup_date字段的直方图边界数据SELECT (unnest(histogram_bounds::text::date[]))::date AS bucket_bound FROM pg_stats WHERE tablename customers AND attname signup_date LIMIT 8; bucket_bound -------------- 2018-01-01 2018-03-01 2018-04-28 2018-07-04 2018-09-05 2018-10-28 2018-12-27 2019-02-14 (8 rows)默认直方图桶的粒度覆盖约两个月数据可满足常规查询估算需求。对于时间、数值分布倾斜的大表增加直方图桶数量可大幅提升范围查询的行数估算精度。直方图精度直接影响规划器判定效果低精度桶仅能判断数据大致分布区间高精度桶可精准识别数据峰值、谷值及细分分布规律为精细化范围查询的计划选择提供精准依据。支持自定义单字段统计精度、增加直方图桶数量ALTER TABLE customers ALTER COLUMN signup_date SET STATISTICS 1000; ANALYZE customers;调整精度并重新采集统计数据后signup_date 字段的直方图桶粒度缩短至约两天数据分布识别精度大幅提升bucket_bound -------------- 2018-01-01 2018-01-03 2018-01-05 2018-01-07 2018-01-10 2018-01-12 2018-01-14 2018-01-16 (8 rows)精度与性能需平衡取舍直方图桶数量越多估算精度越高但会增加ANALYZE计算开销与 SQL 规划开销。不建议修改全局默认统计精度仅针对估算异常的业务字段做定向优化。字段关联统计单字段统计可满足绝大多数单条件查询场景多字段联合筛选场景下规划器默认的字段独立统计逻辑会产生严重估算偏差。执行 city 与 state 双字段联合筛选查询EXPLAIN ANALYZE SELECT * FROM customers WHERE city Cheyenne AND state WY; -- Index Scan on customers (cost... rows8 width...) -- (actual rows4012)规划器估算返回 8 行数据实际查询结果为 4012 行估算偏差达 500 倍。问题核心在于规划器默认所有字段统计相互独立通过多字段独立概率的乘积计算匹配数据量[P(\text{city} \text{Cheyenne} ;\wedge; \text{state} \text{WY}) P(\text{city}) \times P(\text{state})]实际业务数据中city与state字段存在强关联关系city Cheyenne绝大多数隶属于state WY。默认的独立统计逻辑无法识别该关联特征最终导致严重估算偏差。Postgres 10 及以上版本支持自定义扩展统计用于识别字段关联关系CREATE STATISTICS customers_city_state (dependencies, ndistinct) ON city, state FROM customers; ANALYZE customers;dependencies与ndistinct参数分别用于采集字段间关联关系、唯一值关联信息。重新执行ANALYZE更新统计数据后查询计划估算精度大幅优化EXPLAIN ANALYZE SELECT * FROM customers WHERE city Cheyenne AND state WY; -- Index Scan on customers (cost... rows4087 width...) -- (actual rows4012)优化后估算行数 4087 与实际行数 4012 基本吻合。精准的底层行数估算可避免上层关联、聚合逻辑产生连锁错误。底层统计偏差会在多层执行计划中持续放大最终导致复杂查询整体性能劣化。注意扩展统计会轻微增加 ANALYZE 执行开销与查询规划开销仅建议为确认存在字段关联、且存在估算偏差的字段组合创建扩展统计。查询性能快速排查清单当出现异常执行计划、查询效率低下问题时可按照以下固定流程排查优化对比EXPLAIN ANALYZE执行计划中的估算行数与实际行数定位偏差最深的执行节点该节点通常为计划异常的根源。查询目标字段的pg_stats统计指标校验n_distinct唯一值数量、高频值列表是否贴合实际数据特征。大批量数据载入、数据迁移、分区切换后统计数据易过期需手动执行ANALYZE更新实时统计自动真空机制无法及时感知瞬时大批量数据变更。单字段查询存在估算误差时调高字段统计采集精度重新执行ANALYZE刷新统计数据提升数据分布识别准确度。多字段联合查询因字段关联导致估算异常时为对应字段组合创建扩展统计优化跨字段统计精度。彻底排查并修复统计异常问题后再考虑优化 SQL 语句或采用其他调优方案。总结Postgres 查询规划器具备优秀的自动优化能力但所有执行决策均依托系统统计数据而非真实数据表数据。执行计划的精准度完全取决于pg_stats统计信息的质量。pg_stats是洞悉规划器数据判定逻辑的核心窗口绝大多数异常执行计划、SQL 性能问题均源于统计数据与实际业务数据的偏差。遇到不符合预期的EXPLAIN ANALYZE执行结果时需优先排查优化pg_stats统计问题而非强行修改计划参数、盲目重写 SQL。查询规划器的智能程度完全取决于输入的统计数据质量。作者Richard Yen原文链接https://richyen.com/postgres/2026/06/22/pg_stats_how_postgres_internal_stats_work.html