PostgreSQL 长事务中DML产生的数据无法被及时纳入统计信息导致的问题-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 长事务中DML产生的数据无法被及时纳入统计信息导致的问题

简介:
+关注继续查看

PostgreSQL最低的事务隔离级别是read committed,因此在事务中产生的数据变化,在外部是不可见的,包括auto analyze也是不可见的。
例子:

postgres=# show autovacuum;
 autovacuum 
------------
 on
(1 row)

postgres=# show autovacuum_naptime ;
 autovacuum_naptime 
--------------------
 1s
(1 row)

会话A:

postgres=# create table t1(id int, info text);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into t1 select generate_series(1,100000),'test';
INSERT 0 100000
postgres=# insert into t1 select generate_series(1,100000),'test';
INSERT 0 100000

会话B:

postgres=# analyze t1;
ANALYZE
postgres=# select * from pg_stats where attname ='id' and tablename='t1';
 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)

会话A:

postgres=# select * from pg_stats where attname ='id' and tablename='t1';
 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)

在会话A中手工执行完analyze后,生效:

postgres=# analyze t1;
ANALYZE
postgres=# select * from pg_stats where attname ='id' and tablename='t1';
 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct |                                                                                                                                                         
                                                                           most_common_vals                                                                                                                                                  
                                                                                  |                                                                                                                                                          
                                                                                                                                                                                                                                             
                                                                                                                                                                                                          most_common_freqs                  
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
                                                                                                     |                                                                                                                                       
                                                                                                                                                           histogram_bounds                                                                  
                                                                                                                                                                                                                                 | correlatio
n | most_common_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------
--+-------------------+------------------------+----------------------
 public     | t1        | id      | f         |         0 |         4 |  -0.492995 | {1,36,68,79,83,224,241,298,329,345,352,360,408,434,494,558,582,642,688,711,839,865,913,966,975,1078,1164,1297,1315,1323,1338,1357,1376,1515,1516,1545,15
47,1634,1672,1693,1800,1813,1929,1972,1985,2018,2051,2083,2094,2098,2106,2144,2152,2158,2163,2165,2170,2185,2188,2197,2220,2257,2312,2348,2422,2470,2580,2592,2594,2633,2655,2741,2782,2821,2950,2971,3097,3119,3138,3141,3181,3198,3252,3371
,3377,3391,3472,3491,3519,3538,3587,3648,3657,3687,3743,3820,3831,3870,3939,3943} | {6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e
-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.666
67e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.
66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05
,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e
-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05} | {6,1154,2342,3503,4516,5541,6567,7552,8482,9465,10474,11418,12381,13416,14407,15338,16328,17294,18265,19271,20219,21230,22222,23282,24
288,25285,26281,27333,28236,29136,30192,31132,32146,33085,34025,35011,36055,37109,38117,39137,40083,41082,42078,43029,44059,45056,46063,47140,48122,49216,50318,51339,52291,53286,54276,55311,56445,57435,58328,59193,60234,61110,62099,63128
,64152,65140,66126,67172,68166,69115,70155,71057,72166,73199,74246,75218,76172,77205,78274,79284,80261,81186,82187,83093,84065,85087,86065,87065,87993,89044,89990,91032,91986,93040,94077,95122,96070,96990,98051,99020,100000} |    0.50788
9 |                   |                        | 
(1 row)

由于隔离级别的原因,会话B依旧不能读到pg_stats的版本

postgres=# select * from pg_stats where attname ='id' and tablename='t1';
 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)

那么这就会导致一个问题。
如果会话中执行了大量的DML,数据发送了较大的变化。或者会话中有新建的临时表,或者普通表,执行计划可能有问题。
这个问题在复杂查询中尤为突出。

例如有比较多的JOIN的查询,虽然数据量可能非常庞大,但是可能由于没有统计信息就走nest loop了。

优化建议:
在事务中的每条DML语句后面,加一条analyze 响应的表的操作。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
保洁A/B test——用户数据分析的魔力
    在1990年代中期,宝洁公司的高管们开始一项研发全新除异味产品的秘密计划。宝洁公司花费了几百万美元开发了一种无色且成本低廉的液体,这种液体能够喷涂在有油烟味的衬衣、发臭的沙发、陈旧的夹克以及污损的汽车内饰上来除去异味。
1094 0
SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database)
原文:SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 实现代码(SQL Codes) 方法一:运用游...
944 0
ML之FE:基于load_mock_customer数据集(模拟客户,单个DataFrame)利用featuretools工具实现自动特征生成/特征衍生
ML之FE:基于load_mock_customer数据集(模拟客户,单个DataFrame)利用featuretools工具实现自动特征生成/特征衍生
14 0
PostgreSQL · 特性分析 · 统计信息计算方法
一条SQL在PG中的执行过程是: ----> SQL输入 ----> 解析SQL,获取解析后的语法树 ----> 分析、重写语法树,获取查询树 ----> 根据重写、分析后的查询树计算各路径代价,从而选择一条成本最优的执行树 ----> 根据执行树进行执行 ----> 获取结果并返回
1534 0
使用DotNetNuke(DNN) Startkit 4.7(及以上版本)安装无法完成问题的解决方法
最近决定使用DNN Startkit 做些开发,却发现下载DNN 4.8.2 Startkit安装后,生成的网站总是无法完成安装,一到数据库安装那里就停下来,只有进度条滚动,不执行安装的Script。 如图: 几经周折,最终发现自己犯了了一个很愚蠢的错误-没有仔细阅读说明。
656 0
SAP LSMW 物料主数据导入毛重净重放大1000倍问题之对策
准备的物料主数据毛重净重,14.535 KG,导入SAP系统之后,被扩大一千倍,如下图:  经查,在LSMW导入工具里,毛重净重字段类型是N,这是导致导入数据被放大1000倍的根本原因。
1335 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载