ReaderMX.
PostgreSQL学徒

修改列类型又掉坑了！

Pxiongcc约 8 分钟

前言

今天一位同事火急火燎地找过来，说线上有个 SQL 将 CPU 打爆了，紧急 kill 了之后，手动收集了一下统计信息才慢慢恢复，后面经过复盘，发现昨晚开发修改了字段的长度，扩长，而正是这么一个不太起眼的操作导致了今天的血案，让我们一起看看这个有趣的案例！

现象

如前面所述，开发修改了字段类型的长度之后，导致SQL的执行计划发生了改变。让我们复现一下 👇🏻

postgres=# create table t(id int,info varchar(10));
CREATE TABLE
postgres=# create index on t(info);
CREATE INDEX
postgres=# insert into t select n,left(md5(random()::text),10) from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# analyze t;
ANALYZE
postgres=# explain select id,info from t where info = 'hello';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using t_info_idx on t  (cost=0.42..8.44 rows=1 width=15)
   Index Cond: ((info)::text = 'hello'::text)
(2 rows)
postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/2284498
(1 row)
postgres=# select pg_relation_filepath('t_info_idx');
 pg_relation_filepath 
----------------------
 base/13892/2284501
(1 row)

至此还没有任何问题，优化器选择了索引扫描。现在让我们模拟一下昨晚开发做的事情，修改了列的长度（关于修改列长度的注意事项在之前的文章有介绍过，这里就不再重复了）

那么同样操作一下：

postgres=# alter table t alter COLUMN info type varchar(20);
ALTER TABLE
postgres=# explain select id,info from t where info = 'hello';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=16.29..574.78 rows=500 width=62)
   Recheck Cond: ((info)::text = 'hello'::text)
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..16.17 rows=500 width=0)
         Index Cond: ((info)::text = 'hello'::text)
(4 rows)
postgres=# select pg_relation_filepath('t');    ---表未重写
 pg_relation_filepath 
----------------------
 base/13892/2284498
(1 row)
postgres=# select pg_relation_filepath('t_info_idx');  ---索引也未重写
 pg_relation_filepath 
----------------------
 base/13892/2284501
(1 row)

可以看到索引和表没有发生重写，但是执行计划发生了改变！并且预估的行数由最开始的 1 行变成了 500 行！根据过往经验，这个 500 很像是默认选择率的结果，reltuples * DEFAULT_EQ_SEL = 100000 * 0.005 = 500

/* default selectivity estimate for equalities such as "A = b" */
#define DEFAULT_EQ_SEL 0.005
/* default selectivity estimate for inequalities such as "A < b" */
#define DEFAULT_INEQ_SEL  0.3333333333333333

那让我们检查一下统计信息

postgres=# select relpages,reltuples from pg_class where relname = 't_info_idx';
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)
postgres=# select * from pg_stats where tablename = 't' and attname = 'info';
 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)

果然，又双叒叕是统计信息出问题了，什么都没有了，因此优化器选择了一个默认的选择率，重新收集一下统计信息即可。

postgres=# analyze t;
ANALYZE
postgres=# explain select id,info from t where info = 'hello';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using t_info_idx on t  (cost=0.42..8.44 rows=1 width=15)
   Index Cond: ((info)::text = 'hello'::text)
(2 rows)

深入分析

至此此次案例的原因分析清除了。那让我们再深入一下，为何会有这样的操作？老样子当我们不知道代码跑了哪些流程，用原始办法全部抓下来即可

[root@xiongcc postgres]# cat stap.out | sort | uniq  | grep -i statistic
postgres: RemoveStatistics
postgres: transformExtendedStatistics

代码在tablecmd.c里面，RemoveStatistics，很清晰根据名字就可知晓其作用：移除统计信息。

 /*
  * Drop any pg_statistic entry for the column, since it's now wrong type
    因为已经是一个错误的类型了，所以删除pg_statistic中关于此列的信息
  */
 RemoveStatistics(RelationGetRelid(rel), attnum);
/*
 * RemoveStatistics --- remove entries in pg_statistic for a rel or column
 *
 * If attnum is zero, remove all entries for rel; else remove only the one(s)
 * for that column.
 */
void
RemoveStatistics(Oid relid, AttrNumber attnum)
{
 Relation pgstatistic;
 SysScanDesc scan;
 ScanKeyData key[2];
 int   nkeys;
 HeapTuple tuple;
  ...
  /* we must loop even when attnum != 0, in case of inherited stats */
 while (HeapTupleIsValid(tuple = systable_getnext(scan)))  ---删除系统表元组
  CatalogTupleDelete(pgstatistic, &tuple->t_self);
    ...

因此按照我们的分析，不管表上有没有索引，都会移除统计信息，此次案例只是刚好有了一个索引。让我们验证一下：

postgres=# create table t2(info varchar(10));
CREATE TABLE
postgres=# insert into t2 select left(md5(random()::text),10) from generate_series(1,10000) as b;
INSERT 0 10000
postgres=# analyze t2;
ANALYZE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     1
(1 row)
postgres=# alter table t2 alter COLUMN info type varchar(30);
ALTER TABLE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     0
(1 row)
postgres=# select relpages,reltuples from pg_class where relname = 't2';
 relpages | reltuples 
----------+-----------
       55 |     10000
(1 row)

果然 pg_stats 已经没有了关于此列的统计信息。GDB也证实了这一点 👇🏻

(gdb) c
Continuing.
Breakpoint 1, RemoveStatistics (relid=2284514, attnum=1) at heap.c:3246
3246            pgstatistic = table_open(StatisticRelationId, RowExclusiveLock);
(gdb) bt
#0  RemoveStatistics (relid=2284514, attnum=1) at heap.c:3246
#1  0x00000000006a0a90 in ATExecAlterColumnType (tab=0x24ea638, rel=0x248dab8, cmd=0x24e9f58, lockmode=8) at tablecmds.c:12346
#2  0x0000000000691021 in ATExecCmd (wqueue=0x7ffc43a76918, tab=0x24ea638, cmd=0x24e9f58, lockmode=8, cur_pass=1, context=0x7ffc43a76ab0) at tablecmds.c:4996
#3  0x00000000006905e6 in ATRewriteCatalogs (wqueue=0x7ffc43a76918, lockmode=8, context=0x7ffc43a76ab0) at tablecmds.c:4792
#4  0x000000000068fb30 in ATController (parsetree=0x234ed20, rel=0x248dab8, cmds=0x234ecd0, recurse=true, lockmode=8, context=0x7ffc43a76ab0) at tablecmds.c:4388
#5  0x000000000068f78c in AlterTable (stmt=0x234ed20, lockmode=8, context=0x7ffc43a76ab0) at tablecmds.c:4035
#6  0x00000000008ffb09 in ProcessUtilitySlow (pstate=0x24e9ce8, pstmt=0x234f040, queryString=0x234e028 "alter table t2 alter COLUMN info type varchar(30);", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x234f120, qc=0x7ffc43a770b0)
    at utility.c:1317
#7  0x00000000008ff514 in standard_ProcessUtility (pstmt=0x234f040, queryString=0x234e028 "alter table t2 alter COLUMN info type varchar(30);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x234f120, 
    qc=0x7ffc43a770b0) at utility.c:1066
#8  0x00000000008fe715 in ProcessUtility (pstmt=0x234f040, queryString=0x234e028 "alter table t2 alter COLUMN info type varchar(30);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x234f120, qc=0x7ffc43a770b0)
    at utility.c:527
#9  0x00000000008fd65b in PortalRunUtility (portal=0x23b0648, pstmt=0x234f040, isTopLevel=true, setHoldSnapshot=false, dest=0x234f120, qc=0x7ffc43a770b0) at pquery.c:1155
#10 0x00000000008fd854 in PortalRunMulti (portal=0x23b0648, isTopLevel=true, setHoldSnapshot=false, dest=0x234f120, altdest=0x234f120, qc=0x7ffc43a770b0) at pquery.c:1312
#11 0x00000000008fce39 in PortalRun (portal=0x23b0648, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x234f120, altdest=0x234f120, qc=0x7ffc43a770b0) at pquery.c:788
#12 0x00000000008f6f63 in exec_simple_query (query_string=0x234e028 "alter table t2 alter COLUMN info type varchar(30);") at postgres.c:1214
#13 0x00000000008fb20b in PostgresMain (argc=1, argv=0x7ffc43a77340, dbname=0x2378658 "postgres", username=0x2378638 "postgres") at postgres.c:4486
#14 0x000000000084c992 in BackendRun (port=0x23700a0) at postmaster.c:4530
#15 0x000000000084c318 in BackendStartup (port=0x23700a0) at postmaster.c:4252
#16 0x0000000000848a34 in ServerLoop () at postmaster.c:1745
#17 0x0000000000848315 in PostmasterMain (argc=3, argv=0x2348bf0) at postmaster.c:1417
#18 0x000000000075936d in main (argc=3, argv=0x2348bf0) at main.c:209

还有一些其他 case 各位同学就各自测试一下吧

postgres=# create table t2(info varchar(10));         ---修改字段类型会
CREATE TABLE
postgres=# insert into t2 select left(md5(random()::text),10) from generate_series(1,10000) as b;
INSERT 0 10000
postgres=# analyze t2;
ANALYZE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     1
(1 row)
postgres=# alter table t2 alter COLUMN info type text;
ALTER TABLE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     0
(1 row)
postgres=# create table t2(info varchar(10) default null);   ---删除默认值不会
CREATE TABLE
postgres=# insert into t2 select left(md5(random()::text),10) from generate_series(1,10000) as b;
INSERT 0 10000
postgres=# analyze t2;
ANALYZE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     1
(1 row)
postgres=# alter table t2 alter COLUMN info drop default ;
ALTER TABLE
postgres=# select count(*) from pg_stats where tablename = 't2' and attname = 'info';
 count 
-------
     1
(1 row)

根据此函数的调用链，我大致看了一下哪些场景会删除统计信息

Image

  • index_drop，删除索引
  • ALTER COLUMN .. SET DATA TYPE，修改列的类型
  • ALTER TABLE DROP COLUMN ，删除列
  • drop index/constraints，删除索引和约束等

小结

可以看到，修改一个列的类型里面隐藏了太多太多的学问，总结一下：

  • 字段长度或者是精度标度由小变大，可以不需要重写。但是注意对于int4到int8这种转化，还是需要重写的，因为底层存储不一样。
  • 修改非索引列，字段长度由小改大，不会发生重写，假如由大改小，整个分区表所有子表和索引都会发生重写，包括普通表
  • 修改索引列，对于分区表有所出入，字段长度由小改大，虽然所有子表不会重写，但是所有索引会重写！由大改小规则不变，整个分区表和索引都会发生重写
  • 修改列的类型，包括长度、类型，需要重新收集统计信息

真是个有趣的案例。另外 PostgreSQL Architecture 高清无水印大图已经印刷好了，在下一期的官方 PostgreSQL weekly 相信各位就能看到这封大图的消息了，没错我捐献给了社区 ~ 今天疫情解封了，后面安排邮寄

Image

KEEP THE THREAD GOING

更多来源文章