背景：TOAST存储大字段值

TOAST(The Oversized Attributes Storage Technique)机制允许将大字段值在主表行外存储，这有助于管理行大小(page设计为至少存四个rows，每个row不能超过2k)。

创建表时可以设置四种TOAST策略: plain / extend(先压缩再行外存储) / external(直接行外存储) / main(长字段先压缩，如果压缩后放不下，再行外存储)

Explain-Analyze当前存在的问题

大文本字段直接存储在表中比较常见，示例如下：

CREATE TABLE tab_toast (id int, value text);

INSERT INTO tab_toast SELECT n,repeat(nanoid(1024),10*n) FROM generate_series(1,1000) n;

ANALYZE tab_toast ;

上面表的value字段有非常大的值，通过length方法可以查看字段长度，pg_size_pretty可以看出最后一行9MB。

postgres=# SELECT id,length(value),pg_size_pretty(length(value)::bigint) FROM tab_toast WHERE id = any('{1,500,1000}'); 
  id  |  length  | pg_size_pretty 
------+----------+----------------
    1 |    10240 | 10 kB
  500 |  5120000 | 5000 kB
 1000 | 10240000 | 10000 kB
(3 rows)

使用EXPLAIN ANALYZE查看下面语句的真实执行计划，执行时间为0.322ms。

postgres=# EXPLAIN (ANALYZE) SELECT * FROM tab_toast ;
          QUERY PLAN                                             
--------------------------------------
 Seq Scan on tab_toast  (cost=0.00..17.00 rows=1000 width=22) (actual time=0.042..0.319 rows=1000 loops=1)
 Planning Time: 0.119 ms
 Execution Time: 0.322 ms
(3 rows)

这显然不准确，因为缺乏考虑de-toast的影响。

解决方案：Explain-Analyze-Serialize

PG 17增加了Serialize选项可以统计Detoasting以及不同Serialization格式的耗时。

postgres=# EXPLAIN (ANALYZE,SERIALIZE) SELECT * FROM tab_toast ;
          QUERY PLAN                                             
--------------------------------------
 Seq Scan on tab_toast  (cost=0.00..17.00 rows=1000 width=22) (actual time=0.017..1.358 rows=1000 loops=1)
 Planning Time: 0.048 ms
Serialization: time=4051.208 ms  output=5005013kB  format=text
 Execution Time: 4052.920 ms
(4 rows)

上面的执行计划新增了Serialization行，可以看到相比上面的执行计划有更高的执行时间，大约4s，同时执行计划里显示的rows与width计算数据量为21kB，而Serialization里显示输出的数据大约是4GB，数据量相差多倍。

默认的序列化格式为文本方式，下面是binary格式的执行计划：

postgres=# EXPLAIN (ANALYZE,SERIALIZE binary) SELECT * FROM tab_toast ;
          QUERY PLAN                                             
--------------------------------------
 Seq Scan on tab_toast  (cost=0.00..17.00 rows=1000 width=22) (actual time=0.829..1.869 rows=1000 loops=1)
 Planning Time: 0.118 ms
Serialization: time=3631.672 ms  output=5005014kB  format=binary
 Execution Time: 3634.294 ms
(4 rows)

与我联系

• 微信公众号：象楚之行

• 墨天轮：https://www.modb.pro/u/15675

• 微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。