主页 / dba / 问题 / 18864
Accepted
Jmoney38
Asked: 2012-06-06 12:52:08 +0800 CST

查询期间从磁盘检索什么？

  • 772

相当简单的问题，可能在某个地方得到了回答，但我似乎无法为谷歌形成正确的搜索问题......

在查询该表的子集时，特定表中的列数是否会影响查询的性能？

例如，如果表 Foo 有 20 列，但我的查询只选择了其中的 5 列，那么有 20 列（相对于 10 列）会影响查询性能吗？为简单起见，假设 WHERE 子句中的任何内容都包含在这 5 列中。

除了操作系统的磁盘缓存之外，我还担心 Postgres 的缓冲区缓存的使用。我对 Postgres 的物理存储设计的理解非常模糊。表存储在多个页面中（默认为每页 8k 大小），但我不太了解如何从那里排列元组。PG 是否足够聪明，只能从磁盘中获取包含这 5 列的数据？

postgresql performance

3 个回答

  1. Best Answer

    行的物理存储在Database Page Layout中的文档中进行了描述。同一行的列内容都存储在同一个磁盘页面中，除了TOAST的内容（太大而无法放入页面）。内容在每一行中按顺序提取，如下所述：

    要读取数据，您需要依次检查每个属性。首先根据空位图检查该字段是否为NULL。如果是，请转到下一个。然后确保你有正确的对齐方式。如果该字段是一个固定宽度的字段，那么所有的字节都被简单地放置。

    在最简单的情况下（没有 TOAST'ed 列），即使需要很少的列，postgres 也会获取整行。所以在这种情况下，答案是肯定的，拥有更多列可能会对浪费缓冲区缓存产生明显的不利影响，特别是在列内容很大但仍低于 TOAST 阈值的情况下。

    现在是 TOAST 案例：当单个字段超过 ~2kB 时，引擎将字段内容存储到单独的物理表中。当整行不适合一个页面（默认为 8kB）时，它也会发挥作用：一些字段被移动到 TOAST 存储。医生说：

    如果它是一个可变长度字段（attlen = -1），那么它会更复杂一些。所有可变长度数据类型共享一个公共的头结构 struct varlena，它包括存储值的总长度和一些标志位。根据标志，数据可以是内联的，也可以在 TOAST 表中；它也可能被压缩

    当没有明确需要时，不会获取 TOAST 的内容，因此它们对要获取的页面总数的影响很小（每列几个字节）。这解释了@dezso 答案中的结果。

    至于写入，每行及其所有列都会在每次更新时完全重写，无论更改了哪些列。因此，拥有更多列显然对写入来说成本更高。

    • 19

  2. Daniel 的回答侧重于读取单个行的成本。在这种情况下：将固定大小NOT NULL的列放在表中会有所帮助。将相关列放在首位（您查询的列）会有所帮助。通过在列上播放对齐俄罗斯方块来最小化填充（由于数据对齐）可能会有所帮助。但是最重​​要的影响还没有被提及，尤其是对于大桌子。

    附加列显然会使一行占用更多磁盘空间，因此一个数据页上的行数更少（默认为 8 kB）。各个行分布在更多页面上。数据库引擎通常必须获取整个页面，而不是单个行。只要必须读取相同数量的页面，各个行是否更小或更大都无关紧要。

    如果查询获取大表的（相对）一小部分，其中行或多或少随机分布在整个表上，由索引支持，这将导致大致相同数量的页面读取，几乎不考虑到行大小。在这种（罕见的）情况下，不相关的列不会减慢您的速度。

    通常，您将获取已按顺序或邻近输入的补丁或行集群并共享数据页面。这些行由于杂乱而分散，必须读取更多磁盘页面才能满足您的查询。必须阅读更多页面通常是查询变慢的最重要原因。这就是为什么不相关的列会使您的查询变慢的最重要因素。

    对于大型数据库，通常没有足够的 RAM 将其全部保存在高速缓存中。更大的行占用更多的缓存，更多的争用，更少的缓存命中，更多的磁盘 I/O。磁盘读取通常要贵得多。SSD 的情况较少，但仍然存在很大差异。这增加了关于页面读取的上述观点。

    如果不相关的列是 TOAST-ed ，它可能会或可能不会重要。相关的列也可以是 TOAST-ed，带来很多相同的效果。

    • 11

  3. 一个小测试：

    CREATE TABLE test2 (
    id serial PRIMARY KEY,
    num integer,
    short_text varchar(32),
    longer_text varchar(1000),
    long_long_text text
);

INSERT INTO test2 (num, short_text, longer_text, long_long_text)
SELECT i, lpad('', 32, 'abcdefeghji'), lpad('', 1000, 'abcdefeghji'), lpad('', (random() * 10000)::integer, 'abcdefeghji')
FROM generate_series(1, 10000) a(i);

ANALYZE test2;

SELECT * FROM test2;
[...]
Time: 1091.331 ms

SELECT num FROM test2;
[...]
Time: 21.310 ms

    将查询限制为前 250 行 ( WHERE num <= 250) 分别导致 34.539 毫秒和 8.343 毫秒。long_long_text从这个有限的集合中选择除此之外的所有结果会导致 18.432 毫秒。这说明在你看来PG足够聪明。

    • 1

相关问题