PostgreSQL自定义统计信息

最新推荐文章于 2024-11-15 14:06:24 发布

foucus、

最新推荐文章于 2024-11-15 14:06:24 发布

阅读量2k

点赞数

分类专栏： PostgreSQL 文章标签： postgresql

本文链接：https://blog.csdn.net/weixin_39540651/article/details/103928235

版权

pg和oracle的优化器一样是基于成本的(CBO)估算。成本估算中很重要的一个环节是估计每个执行节点返回的记录数。例如在hash join中一般都会选择记录较少的作为hash表。
pg中对于单列选择性的估算比较准确，而对于多列的情况则会出现不准确的情况，因为pg默认使用独立属性，直接以多个字段选择性相乘的方法计算多个字段条件的选择性。
pg10开始支持用户自定义统计信息，这样我们便可以针对这种多列的情况创建自定义多个字段的统计信息，目前支持多列相关性和多列唯一值两种统计。

语法：

CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
    [ ( statistics_kind [, ... ] ) ]
    ON column_name, column_name [, ...]
    FROM table_name

例子：
1、建表

bill=# create table tbl(id int, c1 int, c2 text, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);    
CREATE TABLE

2、插入测试数据

bill=# insert into tbl select 
bill-# id,random()*100, substring(md5(random()::text), 1, 4), random()*900, random()*10000, random()*10000000,     
bill-# random()*100000, random()*100, random()*200000, random()*40000, random()*90000   
bill-# from generate_series(1,1000000) t(id);
INSERT 0 1000000

3、分析表

bill=# analyze tbl ;
ANALYZE

查看规划器中的估计行数值为1e+06

bill=# select reltuples from pg_class where relname='tbl'; 
 reltuples 
-----------
     1e+06
(1 row)

4、查询例子

bill=#  explain (analyze) select * from tbl where c1=1; 
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..16654.33 rows=11000 width=45) (actual time=0.798..53.768 rows=10002 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbl  (cost=0.00..14554.33 rows=4583 width=45) (actual time=0.043..45.096 rows=3334 loops=3)
         Filter: (c1 = 1)
         Rows Removed by Filter: 329999
 Planning Time: 0.212 ms
 Execution Time: 54.445 ms
(8 rows)

可以推算得到c1=1的选择性为: 11000/1e+06

bill=#  explain (analyze) select * from tbl where c2='abc'; 
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..15555.93 rows=16 width=45) (actual time=43.558.

最低0.47元/天解锁文章