sql - View 对聚合函数的性能影响与结果集限制

Question

问题

使用 PostgreSQL 13，我遇到了一个性能问题，从连接两个表的 View 中选择最高 ID，具体取决于我执行的选择语句。

这是一个示例设置:

CREATE TABLE test1 (
  id BIGSERIAL PRIMARY KEY,
  joincol VARCHAR
);

CREATE TABLE test2 (
  joincol VARCHAR
);

CREATE INDEX ON test1 (id);
CREATE INDEX ON test1 (joincol);
CREATE INDEX ON test2 (joincol);

CREATE VIEW testview AS (
SELECT test1.id,
       test1.joincol AS t1charcol,
       test2.joincol AS t2charcol
FROM   test1, test2
WHERE  test1.joincol = test2.joincol
);

我发现了什么

我正在执行两条导致完全不同的执行计划和运行时间的语句。以下语句的执行时间不到 100 毫秒。据我了解执行计划，运行时独立于行数，因为 Postgres 逐行迭代行(从最高 ID 开始，使用索引)直到可以连接一行并立即返回。

SELECT id FROM testview ORDER BY ID DESC LIMIT 1;

但是，这个平均需要超过 1 秒(取决于行数)，因为在 Postgres 使用索引选择最高 ID 之前，这两个表是“完全连接”的。

SELECT MAX(id) FROM testview;

请引用 dbfiddle 上的这个示例来检查解释计划:
https://www.db-fiddle.com/f/bkMNeY6zXqBAYUsprJ5eWZ/1

我的真实环境

在我的真实环境中，test1 只包含一手完整的行 (< 100)，在 joincol 中具有唯一值。 test2 包含多达 ~1000 万行，其中 joincol 始终与 test1 的 joincol 的值相匹配。 test2 的 joincol 不可为空。

实际问题

为什么 Postgres 无法识别它可以在行的基础上使用索引向后扫描进行第二次选择？有什么我可以改进的表/索引吗？

Answer 1

查询不严格等价

why does Postgres not recognize that it could use a Index Scan Backward on row basis for the second select?

为了使上下文清晰:

• max(id)不包括 NULL值。但是ORDER BY ... LIMIT 1没有。
• NULL值按升序排序在最后，在降序中在第一。所以Index Scan Backward可能无法首先找到最大值(根据 max() )，但可以找到任意数量的 NULL值(value)观。

正式等同于:

SELECT max(id) FROM testview;

不是:

SELECT id FROM testview ORDER BY id DESC LIMIT 1;

但是:

SELECT id FROM testview ORDER BY id DESC NULLS LAST LIMIT 1;

后一个查询没有得到快速查询计划。但它会使用具有匹配排序顺序的索引:(id DESC NULLS LAST) .

聚合函数的情况不同 min()和 max() .那些在定位表时得到一个快速计划 test1直接使用 (id) 上的普通 PK 索引.但不是基于 View (或直接底层连接查询 - View 不是阻止程序)。在正确位置对 NULL 值进行排序的索引几乎没有任何效果。

我们知道id在此查询中永远不可能是 NULL .该列定义为 NOT NULL . View 中的连接实际上是一个 INNER JOIN。不能介绍NULL id 的值.
我们也知道 test.id 上的索引不能包含 NULL 值。
但是 Postgres 查询规划器不是 AI。 (也不会尝试这样做，这可能会很快失控。)我看到了两个缺点:

• min()和 max()仅在针对表时获取快速计划，不考虑索引排序顺序，添加索引条件:Index Cond: (id IS NOT NULL)
• ORDER BY ... LIMIT 1仅使用完全匹配的索引排序顺序获取快速计划。

不确定，是否可以(轻松)改进。

db<> fiddle here - 展示以上所有内容

索引

Is there anything I could improve on the tables/indexes?

这个索引完全没用:

CREATE INDEX ON "test" ("id");

PK test.id是通过列上的唯一索引实现的，它已经涵盖了附加索引可能为您做的所有事情。

可能还有更多，等待问题解决。

扭曲的测试用例

测试用例与实际用例相距太远，没有意义。

在测试设置中，每个表有 100k 行，不能保证 joincol 中的每个值另一边有一个匹配项，并且两列都可以为 NULL

您的真实案例在 table1 中有 1000 万行并且 table2 中 < 100 行, table1.joincol 中的每个值在 table2.joincol 中匹配, 两者都被定义为 NOT NULL , 和 table2.joincol是独特的。经典的一对多关系。应该有一个 UNIQUE对 table2.joincol 的约束和 FK 约束 t1.joincol --> t2.joincol .

但是目前这个问题已经完全扭曲了。等待清理干净。