标签
PostgreSQL , 优化器 , 采样 , 多列统计信息 , 行评估 , 大表任意维度过滤行数评估
背景
在任意维度查询（ADHoc）的场景中，有诸多技术手段来保证查询的性能，比如rum索引，GIN倒排索引，BLOOM FILTER索引。又比如多个索引的bitmap 合并scan。
例子：
《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》
《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
《时间、空间、对象多维属性 海量数据任意多维 高效检索 - 阿里云RDS PostgreSQL最佳实践》
《列存优化(shard,大小块,归整,块级索引,bitmap scan) - (大量数据实时读写)任意列搜索》
《多字段，任意组合条件查询(无需建模) - 毫秒级实时圈人 最佳实践》
《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树 (GIN)》
《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》
《PostgreSQL 9.6 黑科技 bloom 算法索引，一个索引支撑任意列组合查询》
但是在有些时候，我们可能想先知道条件输入后，大概有多少复合条件的行（也就是行数评估），PostgreSQL的优化器提供了行评估的功能：
例如我们可以explain的结果来实现高效率的分页估算。
《PostgreSQL pg_stats used to estimate top N freps values and explain rows》
《妙用explain Plan Rows快速估算行 - 分页数估算》
那么在任意字段条件搜索是否也可以用同样的方法呢？多列多条件的评估实际上比单列评估的精度会有所下降，可能导致精度不准确。因此PostgreSQL 10又提供了一种黑科技，允许你对多列构建多列的统计信息。从而提升多字段过滤条件的行评估精准度（会引入一定的统计信息的开销和存储扩展）。
那么在PostgreSQL 10以前，或者是我们不想使用多列统计信息，还有什么好方法来提高多列条件的行评估精准度呢？答案是有的，PG提供了采样的SQL接口，我们可以通过采样数据的计算来评估。
《PostgreSQL Oracle 兼容性 之 - 数据采样与脱敏》
例如在以下文档中，就是使用采样数据来估算TOP N的。
《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》
当然了，PG还提供了一些估算插件，但是仅可用于建模好的数据环境，有兴趣可以阅读如下：
《Greenplum 最佳实践 - 估值插件hll的使用(以及hll分式聚合函数优化)》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 3》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 2》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 1》
《PostgreSQL count-min sketch top-n 概率计算插件 cms_topn (结合窗口实现同比、环比、滑窗分析等) - 流计算核心功能之一》
例子
1、建表，6个字段，写入5000万随机测试数据。
postgres=# create table tbl1(c1 int, c2 int, c3 int, c4 int, c5 int, c6 timestamp);
CREATE TABLE
postgres=# insert into tbl1 select random()*100, random()*10, random()*50, random()*20, random()*5000, clock_timestamp() from generate_series(1,50000000);
INSERT 0 50000000
postgres=# analyze tbl1;
ANALYZE
2、观察多列、单列条件的行评估是否准确
两列AND条件，非常准确（ 评估53562，实际50354 ）
postgres=# select count(*) from tbl1 where c1=1 and c2=1;
count
-------
50354
(1 row)
postgres=# explain select * from tbl1 where c1=1 and c2=1;
QUERY PLAN
-----------------------------------------------------------------------------
Gather (cost=1000.00..686505.00 rows=53562 width=28)
Workers Planned: 2
-> Parallel Seq Scan on tbl1 (cost=0.00..680148.80 rows=22318 width=28)
Filter: ((c1 = 1) AND (c2 = 1))
(4 rows)
两列OR条件，非常准确（ 评估5558119，实际5451751 ）
postgres=# explain select * from tbl1 where c1=1 or c2=1;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on tbl1 (cost=0.00..1117649.92 rows=5558119 width=28)
Filter: ((c1 = 1) OR (c2 = 1))
(2 rows)
postgres=# select count(*) from tbl1 where c1=1 or c2=1;
count
---------
5451751
(1 row)
三列AND条件，非常准确（ 评估10，实际12 ）
postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=99;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..733233.27 rows=10 width=28) (actual time=41.413..1422.724 rows=12 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl1 (cost=0.00..732232.27 rows=4 width=28) (actual time=273.134..1420.060 rows=4 loops=3)
Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 99))
Rows Removed by Filter: 16666663
Planning time: 0.126 ms
Execution time: 1424.440 ms
(8 rows)
但是，所有情况都能准确吗？
当数据出现倾斜时，可能就没那么准确了。
3、再次写入5000万数据，此次c2,c3,c4,c5使用固定值，使其倾斜。
postgres=# insert into tbl1 select random()*100, 10, 50, 20, 5000, clock_timestamp() from generate_series(1,50000000);
INSERT 0 50000000
postgres=# analyze tbl1;
ANALYZE
4、非倾斜值的评估，依旧准确
postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=99;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1465464.64 rows=21 width=28) (actual time=362.359..3084.858 rows=12 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl1 (cost=0.00..1464462.54 rows=9 width=28) (actual time=154.661..3082.340 rows=4 loops=3)
Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 99))
Rows Removed by Filter: 33333329
Planning time: 0.099 ms
Execution time: 3086.687 ms
(8 rows)
5、对于倾斜值，评估偏差很大（ 评估12702，实际0 ）
postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=5000 and c4=20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1570899.53 rows=12702 width=28) (actual time=2834.241..2834.241 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl1 (cost=0.00..1568629.33 rows=5292 width=28) (actual time=2831.777..2831.777 rows=0 loops=3)
Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 5000) AND (c4 = 20))
Rows Removed by Filter: 33333333
Planning time: 0.144 ms
Execution time: 2836.049 ms
(8 rows)
6、调整目标列的统计信息柱状图，扩大到1000。
postgres=# alter table tbl1 alter COLUMN c5 set statistics 1000;
ALTER TABLE
postgres=# alter table tbl1 alter COLUMN c4 set statistics 1000;
ALTER TABLE
postgres=# alter table tbl1 alter COLUMN c1 set statistics 1000;
ALTER TABLE
postgres=# alter table tbl1 alter COLUMN c2 set statistics 1000;
ALTER TABLE
postgres=# analyze tbl1;
ANALYZE
7、行评估依旧不准确（ 评估13034，实际0 ）
postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=5000 and c4=20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1570932.73 rows=13034 width=28) (actual time=2852.150..2852.150 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl1 (cost=0.00..1568629.33 rows=5431 width=28) (actual time=2849.510..2849.510 rows=0 loops=3)
Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 5000) AND (c4 = 20))
Rows Removed by Filter: 33333333
Planning time: 0.181 ms
Execution time: 2853.844 ms
(8 rows)
那么这个问题无解了吗？实际上PG提供了采样查询的接口，我们通过采样查询能够实现高效、准确的评估。
《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》
内置了4种采样接口，我们这里用到的是基于BLOCK级别的百分比采样。
采样百分之一，COUNT值乘以100就是评估行数。如果采样百分之5的话，COUNT值乘以20就是评估行数。
实际上采样百分之一，评估已经非常准确了。
postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 and c5=5000 and c4=20;
count
-------
0
(1 row)
Time: 104.212 ms
postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 and c5=5000 ;
count
-------
0
(1 row)
Time: 107.088 ms
postgres=# select count(*) from tbl1 where c1=1 and c2=1 and c5=5000;
count
-------
2
(1 row)
Time: 2838.372 ms (00:02.838)
postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 ;
count
-------
552
(1 row)
Time: 100.066 ms
postgres=# select count(*) from tbl1 where c1=1 and c2=1 ;
count
-------
50354
(1 row)
Time: 2546.602 ms (00:02.547)
1亿条记录，采样百分之一，大概耗时100毫秒。相比直接全表扫描还是节约了不少成本的。
小结
通过采样查询，我们实现了任意维度条件过滤的行评估，相比传统的行评估算法，精确度提高了不少，在1亿数据量的情况下，采样百分之一，耗时约100毫秒。
参考
《PostgreSQL Oracle 兼容性 之 - 数据采样与脱敏》
《PostgreSQL pg_stats used to estimate top N freps values and explain rows》
《妙用explain Plan Rows快速估算行 - 分页数估算》
《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》
修改单列统计信息柱状图精度，默认是100全局default_statistics_target参数控制。
postgres=# alter table tbl1 alter COLUMN c5 set statistics 1000;
ALTER TABLE
Time: 0.786 ms
postgres=# analyze tbl1;
ANALYZE
Time: 2157.194 ms (00:02.157)
《Greenplum 最佳实践 - 估值插件hll的使用(以及hll分式聚合函数优化)》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 3》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 2》
《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 1》
《PostgreSQL count-min sketch top-n 概率计算插件 cms_topn (结合窗口实现同比、环比、滑窗分析等) - 流计算核心功能之一》
