sql - 您会推荐哪些优化工作来加速 PostgreSQL 中的查询执行？

Question

我有名为 ORGANIZATIONAL_STRUCTURE_HISTORY 和 SURVEYS_ORGANIZATIONS_RELATIONSHIP 的表。我使用下一个版本的 PostgreSQL 数据库:

PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

现在我在下面使用非常简单的 SQL 查询，两个表中的条目数都非常少。假设每周大约有 100 万条记录将填充到表中。我试图找出请求中的大量数据瓶颈是什么。

SELECT
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_NAME,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_RANG,
    ORGANIZATIONAL_STRUCTURE_HISTORY.PARENT_ORGANIZATION_ID
FROM
    ORGANIZATIONAL_STRUCTURE_HISTORY
WHERE
    ORGANIZATIONAL_STRUCTURE_HISTORY.SURVEY_ID = 'bc90de33-62f9-4c6f-a7a6-6a76abb28b65'
AND 
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID IN(
    SELECT
        ORGANIZATION_ID
    FROM
        SURVEYS_ORGANIZATIONS_RELATIONSHIP
    WHERE
        SURVEY_ID = 'bc90de33-62f9-4c6f-a7a6-6a76abb28b65'
)
ORDER BY
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_RANG,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID;

我找到了旧的article作者解释了为什么 IN 子句在具有大量数据的表中存在问题。不太确定这是否仍然与我感到困惑的原因有关。

我将查询更改为下一个代码:

SELECT
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_NAME,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_RANG,
    ORGANIZATIONAL_STRUCTURE_HISTORY.PARENT_ORGANIZATION_ID
FROM
    ORGANIZATIONAL_STRUCTURE_HISTORY
JOIN SURVEYS_ORGANIZATIONS_RELATIONSHIP
    ON ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID = SURVEYS_ORGANIZATIONS_RELATIONSHIP.ORGANIZATION_ID
WHERE
    ORGANIZATIONAL_STRUCTURE_HISTORY.SURVEY_ID = 'bc90de33-62f9-4c6f-a7a6-6a76abb28b65'
AND
    SURVEYS_ORGANIZATIONS_RELATIONSHIP.SURVEY_ID = 'bc90de33-62f9-4c6f-a7a6-6a76abb28b65'
ORDER BY
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_RANG,
    ORGANIZATIONAL_STRUCTURE_HISTORY.ORGANIZATION_ID;

第二个查询的 EXPLAIN 命令返回关闭结果。

问题:您建议进行哪些优化工作？

Answer 1

这两个查询是不同的；如果您希望它们相同，则必须将 DISTINCT 添加到第二个，除非您知道每个 ORGANIZATIONAL_STRUCTURE_HISTORY< 只能有一个 SURVEYS_ORGANIZATIONS_RELATIONSHIP/.

我假设第一个查询是您真正想要的。

您应该重写查询；而不是

... WHERE a.x IN
       (SELECT y FROM b
        WHERE pred)

使用

... WHERE EXISTS
       (SELECT 1 FROM b
        WHERE pred
          AND a.x = b.y)

这两个索引非常适合查询:

CREATE INDEX ON SURVEYS_ORGANIZATIONS_RELATIONSHIP
   (survey_id, organization_id);

CREATE INDEX ON ORGANIZATIONAL_STRUCTURE_HISTORY
   (survey_id, organization_rang, organization_id);

我在这里做的额外假设是 survey_id 的条件是选择性的。