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

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 响应的表的操作

(责任编辑：好模板)