主页 / dba / 问题 / 190156
Accepted
ldrg
Asked: 2017-11-06 08:42:47 +0800 CST

Postgres 可以对这个带有连接表的查询使用仅索引扫描吗？

  • 772

这是对以下内容的后续：PostgreSQL 中的覆盖索引是否有助于 JOIN 列？

考虑在连接表中过滤的另一个问题中模式的逆：

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

像这样的查询：

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

有一个像这样的索引是不是很疯狂：

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

哪个涵盖了用于该连接的主键？该索引会被用作覆盖索引来帮助JOIN上述查询吗？当我知道要JOIN像这样编辑表时，是否应该更改索引策略以更频繁地覆盖主键？

postgresql index

2 个回答

  1. Best Answer

    如果满足仅索引扫描的其他先决条件，则将列id作为尾随列附加到索引（而不是前导列）是非常有意义的：

    CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

    Postgres 11使用关键字引入了实际的覆盖索引INCLUDE。

    CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type) INCLUDE (id);

    对您的情况只有一点好处，但将列添加到 UNIQUE 或 PK 索引或约束是一个很好的选择。

    关于仅索引扫描：

    最重要的前提条件：表的可见性映射thing_types必须将大部分或所有页面显示为对所有事务“可见”。即表要么是只读的，要么你的自动清理设置足够激进，可以在写入表后持续清理。

    每增加一个索引都会增加成本。主要是写性能。但也有副作用，例如耗尽的缓存容量。（使用相同索引的多个查询有更好的机会将它们驻留在缓存中。）所以这也是一个size的问题。id通常是一个非常小的列integer或bigint. 使其成为用例的良好候选者。

    特别是，将列添加到索引会禁用涉及该列的 HOT 更新选项。但是由于id无论如何都已编入索引并且通常不会更新（作为 PK），因此在这种情况下这不是问题。有关的：

    如果您实际上大部分时间都从这些索引中获得仅索引扫描，那么使用它们通常是有意义的。用 测试EXPLAIN。

    旧版本中的部分索引存在限制。引用 Postgres 9.6的发行说明：

    • 当索引的子句引用未编入索引的列时，允许对部分索引使用仅索引扫描（Tomas Vondra，Kyotaro Horiguchi）WHERE

      例如，由 定义的索引CREATE INDEX tidx_partial ON t(b) WHERE a > 0现在可以用于指定WHERE a > 0且不使用的查询的仅索引扫描a。以前这是不允许的，因为 a 未列为索引列。

    • 3

  2. 您需要尝试一下，看看您的具体查询计划。您对给出的建议甚至对您的查询有用的可能性做出了很多笼统的假设。

    • 尺寸。
    • PostgreSQL 主编号
    • 成本配置。
    • 统计数据的陈旧性和准确性。

    所有这些事情都很重要。

    在这里不要含糊不清，但我可以想出几个例子来向你展示这一点。

    通常，我不会索引表中已经编入索引的内容。如果除了因为每次索引覆盖特定列之外没有其他原因，那么当您更改行时，您还有一个必须更新的索引。

    • 0

相关问题