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