PostgreSQL复杂列依赖引发查询性能问题及统计相关疑问

问题背景 #

有一张表mytable，字段包括：

• id：SERIAL类型主键
• column1：INTEGER类型，默认0，取值仅0/1
• column2：TEXT类型，取值仅'A'/'B'/'C'
• departmentid：INTEGER类型

数据分布特点：

• column2为'A'/'B'时，column1大多为0
• column2为'C'时，column1整体分布是0占15%、1占85%；但部分departmentid下，column2='C'时column1全为1

执行以下查询时：

SELECT * FROM mytable WHERE departmentid = 42 AND column2 = 'C' AND column1 = 0 ORDER BY id LIMIT 10;

PostgreSQL选择基于id的索引扫描，预估返回约25000行，但实际无匹配数据，导致全表扫描耗时极长；若使用位图扫描，速度可提升50倍。表中已存在各列的单独B-tree索引。

疑问 #

1. 创建了包含column1、column2的统计对象mytable_column2_column1，以及包含departmentid、column1、column2的统计对象mytable_departmentid_column1_column2，执行ANALYZE后，为何column2到column1的依赖为空？（后续发现存在departmentid+column2到column1的依赖，但无column2到column1的依赖）
2. 如何不创建(departmentid, column2, column1, id)四列索引来加速该查询？因为实际生产中存在多种排序和过滤条件，该索引仅能覆盖默认场景。
3. 是否可通过修改DDL解决此问题？
4. 是否有类似corr()的函数可查询字段值之间的依赖关系？

补充信息 #

PostgreSQL版本 #

16.1，每日自动执行VACUUM和ANALYZE，表数据量约300万行。

表定义（仅展示相关字段） #

CREATE TABLE mytable
(
  id           SERIAL
    PRIMARY KEY,
  column1      INTEGER DEFAULT 0 NOT NULL,
  column2      TEXT              NOT NULL,
  departmentid INTEGER
);
CREATE INDEX mytable_departmentid_index
    ON mytable (departmentid);
CREATE INDEX mytable_column1_index
    ON mytable (column1);
CREATE INDEX mytable_column2_index
    ON mytable (column2);

-- 已创建的统计对象
CREATE STATISTICS mytable_column2_column1 ON column2, column1 FROM mytable;
CREATE STATISTICS mytable_departmentid_column1_column2 ON departmentid, column1, column2 FROM mytable;

departmentid的空值占比<0.0002%，统计信息显示无空值。

查询执行计划（实际数据） #

Limit  (cost=0.68..501.79 rows=10 width=2690) (actual time=35351.049..47175.738 rows=1 loops=1)
  Output: id, column1, column2, departmentid
  Buffers: shared hit=1682274 read=1646793 dirtied=1640 written=980
  I/O Timings: shared/local read=39193.882 write=9.565
  WAL: records=1637 fpi=1637 bytes=3034199
  ->  Index Scan using mytable_pkey on public.mytable  (cost=0.68..1392081.41 rows=27780 width=2690) (actual time=35351.048..47175.735 rows=1 loops=1)
        Output: id, column1, column2, departmentid
        Filter: ((mytable.departmentid = 42) AND (mytable.column2 = 'C'::text) AND (mytable.column1 = 0))
        Rows Removed by Filter: 3536431
        Buffers: shared hit=1682274 read=1646793 dirtied=1640 written=980
        I/O Timings: shared/local read=39193.882 write=9.565
        WAL: records=1637 fpi=1637 bytes=3034199
Settings: effective_cache_size = '8008368kB', effective_io_concurrency = '0', geqo_effort = '10', jit = 'off', random_page_cost = '1.2', search_path = 'public'
Planning Time: 0.503 ms
Execution Time: 47175.783 ms

扩展统计信息（pg_stats_ext） #

基础统计信息（pg_stats） #

解答 #

1. 为何column2到column1的依赖为空？ #

PostgreSQL的扩展统计中，依赖关系仅在条件依赖足够强时才会被记录。从你的扩展统计数据看，mytable_column2_column1的联合频率与独立假设下的基础频率差异不大：column2='C'时column1=0的实际频率是0.13586，基础频率是0.15970；column2='A'/'B'时的差异也未达到触发依赖统计的阈值。

另外，departmentid的存在让column2和column1的依赖是条件性的——只有结合departmentid，依赖关系才会显著（比如某些部门下column2='C'时column1全为1），所以三列统计对象能检测到departmentid+column2到column1的依赖，但两列统计中整体依赖强度不足，因此没有生成column2 => column1的依赖记录。

2. 不创建四列索引如何加速查询？ #

方法1：强制使用位图扫描

可以通过查询提示或临时参数强制优化器选择位图扫描：

-- 使用查询提示（PostgreSQL 12+支持）
SELECT /*+ BitmapScan(mytable) */ * FROM mytable WHERE departmentid = 42 AND column2 = 'C' AND column1 = 0 ORDER BY id LIMIT 10;

-- 或临时关闭索引扫描
SET enable_indexscan = off;
SELECT * FROM mytable WHERE departmentid = 42 AND column2 = 'C' AND column1 = 0 ORDER BY id LIMIT 10;
SET enable_indexscan = on;

方法2：提升统计精度

调整列的统计目标，让优化器更准确预估行数：

ALTER TABLE mytable ALTER COLUMN departmentid SET STATISTICS 1000;
ALTER TABLE mytable ALTER COLUMN column2 SET STATISTICS 1000;
ANALYZE mytable;

你的扩展统计对象已包含d（依赖）和f（频率）类型，无需调整。

方法3：创建灵活的索引

创建三列组合索引或部分索引，适配更多场景：

-- 三列组合索引，适配包含这三个过滤条件的各类查询
CREATE INDEX mytable_dept_col2_col1_idx ON mytable (departmentid, column2, column1);

-- 针对特定场景的部分索引，快速过滤无数据的情况
CREATE INDEX mytable_dept42_col2c_col1_idx ON mytable (column1) WHERE departmentid = 42 AND column2 = 'C';

3. 是否可通过修改DDL解决此问题？ #

可以尝试以下DDL调整：

• 将column2改为ENUM类型：TEXT类型的统计精度不如ENUM，ENUM取值更明确，优化器能更准确计算频率：

  CREATE TYPE column2_enum AS ENUM ('A', 'B', 'C');
  ALTER TABLE mytable ALTER COLUMN column2 TYPE column2_enum USING column2::column2_enum;
  

• 添加部分约束：如果某些departmentid下column2='C'时column1必须为1，添加约束让优化器明确该场景下column1=0无数据：

  ALTER TABLE mytable ADD CONSTRAINT dept_col2c_col1_check CHECK (NOT (departmentid = 42 AND column2 = 'C' AND column1 = 0));
  

  该约束会让优化器直接预估返回0行，避免错误的索引扫描选择。

4. 查询字段依赖关系的方式 #

PostgreSQL没有直接返回字段依赖关系的函数，但可以通过以下方式查看：

• 查询pg_stats_ext系统视图，dependencies字段存储了检测到的依赖关系，格式为{X => Y: 系数}，系数越接近1，依赖越强。
• 对比pg_stats和扩展统计中的most_common_freqs与most_common_base_freqs，联合频率与独立假设下的基础频率差异越大，说明依赖越强。
• 自定义SQL计算条件概率，判断依赖强度：

  SELECT 
    COUNT(*) FILTER (WHERE column2 = 'C' AND column1 = 0)::FLOAT / COUNT(*) FILTER (WHERE column2 = 'C') AS cond_prob
  FROM mytable;
  

  将结果与column1=0的整体频率（0.17056）对比，差异越大说明依赖越强。

内容的提问来源于stack exchange，提问作者Bunny Boss