我最近在一个结构有些低效的数据库中看到了一些非常宽泛的表（数百列）。 我们的 PostgreSQL 支持客户抱怨无法轻易解释的奇怪运行时行为。 为了帮助遇到同样情况的其他 PostgreSQL 用户，我决定揭示一个很多人不理解的相当常见的性能问题的秘密：列顺序和列访问。
创建一个大表
第一个问题是：我们如何创建一个包含许多列的表？ 最简单的方法是使用 generate_series 简单地生成 CREATE TABLE 语句：
test=# SELECT 'CREATE TABLE t_broad (' || string_agg('t_' || x || ' varchar(10) DEFAULT ''a'' ', ', ') || ' )' FROM generate_series(1, 4) AS x; ?column? ---------------------------------------------------------- CREATE TABLE t_broad ( t_1 varchar(10) DEFAULT 'a' , t_2 varchar(10) DEFAULT 'a' , t_3 varchar(10) DEFAULT 'a' , t_4 v archar(10) DEFAULT 'a' ) (1 row) test=# \gexec CREATE TABLE复制
为了简单起见，我在这里只使用了 4 列。 生成命令后，我们可以使用 \gexec 执行我们刚刚编译的字符串。 \gexec 是一个非常强大的东西：它将先前的结果视为 SQL 输入，这正是我们在这里想要的。 它给我们留下了一个包含 4 列的表格。
但是，让我们删除表格并创建一个非常大的表格。
test=# DROP TABLE t_broad ; DROP TABLE复制
创建一个非常宽的表
以下语句创建一个包含 1500 列的表。 请注意，上限为 1600 列：
test=# SELECT 'CREATE TABLE t_broad (' || string_agg('t_' || x || ' varchar(10) DEFAULT ''a'' ', ', ') || ' )' FROM generate_series(1, 1500) AS x;复制
在现实生活中，这样的表远非高效，通常不应该用于存储数据。 它只会产生过多的开销，并且在大多数情况下，它一开始就不是好的建模。
让我们填充表并添加 100 万行：
test=# \timing Timing is on. test=# INSERT INTO t_broad SELECT 'a' FROM generate_series(1, 1000000); INSERT 0 1000000 Time: 67457,107 ms (01:07,457) test=# VACUUM ANALYZE ; VACUUM Time: 155935,761 ms (02:35,936)复制
请注意，该表具有默认值，因此我们可以确定这些列实际上包含某些内容。 最后，我执行了 VACUUM 以确保设置了所有提示位等。
我们刚刚创建的表大小约为 4 GB，可以使用以下行轻松确定：
test=# SELECT pg_size_pretty(pg_total_relation_size('t_broad')); pg_size_pretty ---------------- 3907 MB (1 row)复制
访问各种列
PostgreSQL 按行存储数据。 正如您可能知道的那样，数据可以按列或行存储。 根据您的用例，一个或另一个选项可能是有益的。 在 OLTP 的情况下，基于行的方法通常效率更高。
让我们做一个计数（*），看看需要多长时间：
test=# SELECT count(*) FROM t_broad; count --------- 1000000 (1 row) Time: 416,732 ms复制
我们可以在大约 400 毫秒内运行查询，这非常好。 正如预期的那样，优化器将进行并行顺序扫描：
test=# explain SELECT count(*) FROM t_broad; QUERY PLAN -------------------------------------------------------------------- Finalize Aggregate (cost=506208.55..506208.56 rows=1 width=8) -> Gather (cost=506208.33..506208.54 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=505208.33..505208.34 rows=1 width=8) -> Parallel Seq Scan on t_broad (cost=0.00..504166.67 rows=416667 width=0) JIT: Functions: 4 Options: Inlining true, Optimization true, Expressions true, Deforming true (8 rows)复制
让我们将此与第一列的计数进行比较。 您会看到性能上的细微差别。 原因是 count(*) 必须检查行是否存在，而 count(column) 必须检查是否将 NULL 值提供给聚合。 如果为 NULL，则必须忽略该值：
test=# SELECT count(t_1) FROM t_broad; count --------- 1000000 (1 row) Time: 432,803 ms复制
但是，让我们看看如果我们访问第 100 列会发生什么？ 这样做的时间将有很大不同：
test=# SELECT count(t_100) FROM t_broad; count --------- 1000000 (1 row) Time: 857,897 ms复制
执行时间基本上翻了一番。 如果我们对第 1000 列进行计数，性能会更差：
test=# SELECT count(t_1000) FROM t_broad; count --------- 1000000 (1 row) Time: 8570,238 ms (00:08,570)复制
哇，我们已经比以前慢了 20 倍。这不是一个小区别，而是一个必须理解的主要问题。
揭穿 PostgreSQL 性能问题：列顺序
首先要理解为什么会出现这个问题，我们需要看一下 PostgreSQL 是如何存储数据的：在每一行中存在的元组标头之后，我们得到了几个 varchar 列。我们只是在这里使用了 varchar 来证明这一点。其他数据类型也会出现同样的问题——这个问题在 varchar 中更为明显，因为它在内部比整数更复杂。
PostgreSQL 如何访问列？它将获取行，然后剖析此元组以计算所需列在行内的位置。因此，如果我们想要访问列 #1000，这意味着我们必须计算出在我们选择的列之前的前 999 列有多长。这可能非常复杂。对于整数，我们只需添加 4，但对于 varchar，操作变得非常昂贵。让我们看看 PostgreSQL 是如何存储 varchar 的（只是想看看它为什么这么贵）：
- 1 位表示短（127 字节）与长字符串（> 127 位）
- 7 位或 31 位长度（取决于第一位）
- “数据” + \0（终止字符串）
- 对齐（确保下一列以 CPU 字长的倍数开始）
现在想象一下，如果我们需要循环超过 1000 列，这意味着什么？它确实会产生一些重要的开销。
最后 …
这里的关键见解是，从性能的角度来看，使用非常大的表通常没有好处。使用合理的表格布局在性能和便利性之间取得良好的折衷是有意义的。
如果您对其他提高性能的方法感兴趣，请阅读我在 CLUSTER 上的博客。
原文标题：Column order in PostgreSQL does matter
原文作者：Hans-Jürgen Schönig
原文地址：https://www.cybertec-postgresql.com/en/column-order-in-postgresql-does-matter/
