PostgreSQL 教程: 检查表的列相关性

二月 27, 2024

摘要：在本教程中，您将学习如何在 PostgreSQL 中检查一个表的列相关性。

目录

在 PostgreSQL 中，当你使用ANALYZE对一个表收集完值分布的统计信息后，你会在 pg_stats 系统视图中找到收集的每个列的统计信息。本文将解释correlation列的含义及其对索引扫描的影响。

物理排序与逻辑排序

大多数常见的 PostgreSQL 数据类型都具有一种顺序性：它们支持运算符<，<=，=，>=和>。这些数据类型都支持创建 B 树索引（“标准”索引类型）。

这种类型的列中的值提供了一种表行的逻辑顺序。此列上的索引会根据该顺序进行排序。

PostgreSQL 中的表由一个或多个文件组成，数据文件中以 8 KB 大小的块作为最小单元。表行在文件中的实际存储顺序是物理顺序。您可以通过查看ctid系统列来检查行的物理顺序，该列包含了块编号和块内的项编号，这些编号描述了表行的物理位置。

相关性

列的相关性是一个介于 -1 和 1 之间的值。它说明了逻辑顺序和物理顺序之间的匹配程度。

  • 如果相关性为 1，则表行按列值升序存储在表文件中；如果相关性为 -1，则按降序存储。
  • 介于 -1 和 1 之间的值表示不太完美的匹配。
  • 值为 0 表示物理顺序和逻辑顺序之间没有联系。

为什么需要关心它？

为了更快地访问表，您会在表上创建索引（但不要太多！）。列的相关性会影响索引扫描的性能。

在索引扫描期间，整个或部分的索引会按索引顺序被读取。对于找到的每条记录，从表中获取相应的行（在“仅索引扫描”中无需访问表行，但这是另一回事）。

如果索引列的相关性接近于零，则获取的行会分散在表中的各个块里面。这会导致对许多随机分布的不同表块的读取。

但是，如果相关性接近 1 或 -1，则在索引扫描期间获取的下一行，往往会与前一行位于同一个表块中，或者下一个表块中。

相关性高有两个优点：

  1. 数据库读取的块缓存在共享内存中。因此，如果在索引扫描期间获取的许多表行，位于同一个表块中，则只需从存储设备读取很少的块。
  2. 必须从存储设备读取的块彼此相邻。这会触发顺序 I/O，在旋转式磁盘上，顺序 I/O 比随机 I/O 快得多。

示例

让我们来创建两个内容相同但相关性不同的表：

CREATE TABLE corr (id, val) AS
   SELECT i, 'some text ' || i
   FROM generate_series(1, 100000) AS i;

CREATE INDEX corr_idx ON corr (id);

VACUUM (ANALYZE) corr;

SELECT correlation FROM pg_stats
WHERE tablename = 'corr' AND attname = 'id';

 correlation
-------------
           1
(1 row)

CREATE TABLE uncorr AS
   SELECT * FROM corr
   ORDER BY random();

CREATE INDEX uncorr_idx ON uncorr (id);

VACUUM (ANALYZE) uncorr;

SELECT correlation FROM pg_stats
WHERE tablename = 'uncorr' AND attname = 'id';

 correlation
-------------
 -0.00522369
(1 row)

我们来禁用一下位图索引扫描，以便可以比较两个表上的索引扫描。然后我们检查下索引扫描是如何执行的：

SET enable_bitmapscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM corr WHERE id BETWEEN 1001 AND 1300;

                    QUERY PLAN
---------------------------------------------------
 Index Scan using corr_idx on corr
       (cost=0.29..15.23 rows=297 width=19)
       (actual time=0.108..0.732 rows=300 loops=1)
   Index Cond: ((id >= 1001) AND (id <= 1300))
   Buffers: shared hit=6
 Planning time: 0.456 ms
 Execution time: 1.049 ms
(5 rows)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM uncorr WHERE id BETWEEN 1001 AND 1300;

                    QUERY PLAN
---------------------------------------------------
 Index Scan using uncorr_idx on uncorr
       (cost=0.29..978.15 rows=298 width=19)
       (actual time=0.105..2.352 rows=300 loops=1)
   Index Cond: ((id >= 1001) AND (id <= 1300))
   Buffers: shared hit=303
 Planning time: 0.548 ms
 Execution time: 2.736 ms
(5 rows)

现在耗时 2.7 毫秒还不算太坏，但这只是因为所有的块都已经在共享缓冲区中了。如果这些块中的一部分必须从磁盘读取，那么读取第二个查询的 303 个块将比读取第一个查询的 6 个块表现差得多！

在第二个查询中，每个结果行都在不同的表块中找到。这触发访问了 300 个表块。其余三个块是索引块。

第一个查询只访问了三个表块：

SELECT ctid, id FROM corr
WHERE id BETWEEN 1001 AND 1300;

  ctid   |  id
---------+------
 (6,58)  | 1001
 (6,59)  | 1002
 (6,60)  | 1003
 (6,61)  | 1004
 (6,62)  | 1005
 (6,63)  | 1006
 (6,64)  | 1007
 ...
 (8,37)  | 1294
 (8,38)  | 1295
 (8,39)  | 1296
 (8,40)  | 1297
 (8,41)  | 1298
 (8,42)  | 1299
 (8,43)  | 1300
(300 rows)

实际上，所有的行都包含在表块 6、7 和 8 中！

相关性和优化器

PostgreSQL 优化器会评估可能的执行路径的成本，来执行一条 SQL 语句。

通过使用相关性，它可以对一次索引扫描的成本进行更好的估计，从而选择出更好的执行计划。

如果相关性接近 1 或 -1，PostgreSQL 优化器会优先选择索引扫描。

相关性和 BRIN 索引

PostgreSQL 9.5 引入了 BRIN 索引（块范围索引）。

这种索引的工作原理是存储各段范围内表块中所有值的最小值和最大值。它只对具有高相关性的列有用。与 B 树索引相比，它的优点是索引的大小要小得多，这使得它对于大型表是一个有意思的选择。

如何利用相关性？

如果需要高效地扫描索引的较大部分，最好让表保持按索引顺序存储。

PostgreSQL 中没有“索引组织表”。尽管如此，可以通过两种方式来保持列的高相关性：

  1. 自动：如果表行是按逻辑列顺序插入的，并且表上没有更新或删除，则物理顺序将与逻辑顺序相同。典型的场景是由序列生成的主键列，或带有一个时间戳的测量数据。

    由于在这种情况下相关性总是很高，建立一个 BRIN 索引可能是有意义的。

    如果您想从表中删除旧数据而不破坏物理顺序，可以使用表分区。

  2. 聚集：SQL 命令CLUSTER可以用来重写一个表，使物理顺序和一个索引的逻辑顺序相同。

    然而，后续对该表的修改会再次降低相关性。因此，您需要定期重新对表进行聚集，以保持高相关性。这很烦人，因为CLUSTER会阻止所有对表的并发访问。

了解更多

PostgreSQL 监控

搜索

分类

标签