主页 / dba / 问题 / 190132
Accepted
ldrg
Asked: 2017-11-05 19:18:50 +0800 CST

PostgreSQL 中的覆盖索引是否有助于 JOIN 列？

  • 772

我有很多看起来像这样的表格：

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

我做了很多连接，我试图过滤加入的表以从第一个表中获取内容，如下所示：

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

当我为表编写索引时，我会查看在 WHERE 子句中使用的列并构建索引以满足它们。所以对于这个查询，我最终会写一个这样的索引：

CREATE INDEX ON table2 (t2c1);

并且该索引至少可以在该查询中使用。

我的问题是，如果我编写这样的索引：

CREATE INDEX ON table2 (t2c1, t1);

是否会将该索引用作覆盖索引来帮助上述查询中的 JOIN？我应该改变我的索引编写策略来覆盖外键列吗？

postgresql index

2 个回答

  1. Best Answer

    是否会将该索引用作覆盖索引来帮助上述查询中的 JOIN？

    这取决于。Postgres 将“仅索引”扫描作为索引访问方法，直到 Postgres 10 本身都没有“覆盖索引”。

    从 Postgres 11 开始，可以使用带有列的真正覆盖索引INCLUDE。Michael Paquier 的博客文章介绍了该功能：

    与代码示例相关的答案：

    也就是说，索引CREATE INDEX ON table2 (t2c1, t1);对于您演示的查询非常有意义。如果满足其他先决条件，它可以用于仅索引扫描，也可以用于位图索引扫描或普通索引扫描。有关的：

    JOIN在 Postgres 中条件和WHERE条件几乎完全等价。他们当然可以以同样的方式使用索引。您可以重写您的查询：

    SELECT t1.t1c1
FROM   table1 t1
JOIN   table2 t2 ON t2.t1 = t1.id
WHERE  t2.t2c1 = 42;

    有了这个等价物：

    SELECT t1.t1c1
FROM   table1 t1 CROSS JOIN table2 t2
WHERE  t2.t1 = t1.id
AND    t2.t2c1 = 42;

    不过，第一种形式显然更可取。更容易阅读。

    为什么“几乎”等价？（对于手头的简单查询没有区别。）

    有关的：

    • 22

  2. 是否会将该索引用作覆盖索引来帮助上述查询中的 JOIN？我应该改变我的索引编写策略来覆盖外键列吗？

    在上述查询中不太可能。这是一个具有欺骗性的复杂问题，其结果基于两个条件的估计和选择性，

    • 表 2.t1 = 表 1.id
    • t2c1 = 42

    本质上，您希望同时抛出两个环境（行数）以使其具有或多或少的选择性。如果你得到一个嵌套循环，你想增加原始数量，直到它不再是最可行的连接方法。

    CREATE TABLE table1(
   id INTEGER PRIMARY KEY,
   t1c1 INTEGER,
   t1c2 INTEGER
);
INSERT INTO table1(id, t1c1, t1c2)
  SELECT x,x,x FROM generate_series(1,1000)
  AS gs(x);

CREATE TABLE table2(
  id INTEGER PRIMARY KEY,
  t1 INTEGER REFERENCES table1(id),
  t2c1 INTEGER
);
INSERT INTO table2(id, t1, t2c1)
SELECT x,1+x%1000,x%50 FROM generate_series(1,1e6)
  AS gs(x);

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

    现在检查计划。

    现在创建复合索引，

    CREATE INDEX ON table2 (t2c1, t1);
VACUUM FULL ANALYZE table1;
VACUUM FULL ANALYZE table2;

    并再次检查计划，

    EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

    您可以删除键等以找到它喜欢的形式

    CREATE INDEX ON table2 (t1, t2c1);

    或者

    CREATE INDEX ON table2 (t2c1, t1);

    最终，虽然这是很多工作，但我建议从

    CREATE INDEX ON table2 (t1);
CREATE INDEX ON table2 (t2c1);

    并且仅在这还不够的情况下进行优化。

    您还可以禁用特定的计划器选项，以查看另一个计划是否真的更快或更慢，然后寻求解决该问题，但这也可能需要大量工作。

    • 4

相关问题