解决 PostgreSQL com CTE 的性能问题

剧透：CTE 的 apenas 转换或线性二次问题的查询速度可达 23 倍。语义索引。

一些常见的软件性能问题与常见的嵌套循环有关。

与其他银行不同。为了解决这一问题，我们提出了一个关于二次复杂性的问题，这是我们第一次解决问题的方法，通过一系列性能指标来解决。

您可以选择解决嵌套循环问题、查询语句、使用CTE或公共表表达式的问题。

设置

基本设置 3 个选项：

• 200 位用户
• 20家银行
• 4000 名银行账户
• 4000 次转移 不同之处之间的转移

注意：没有最后的内容比较或链接或要点或完整。

德萨菲奥

一个想法是，查询返回一个列表，其中包含不同的银行和计算相关的信息：

查询com múltiplos JOIN的e SUM聚合

原则上，我们可以查询用户的信息、用户的信息、银行的JOIN信息、账户和转账等信息：

SELECT
    users.name AS username,
    banks.name AS bank,
    SUM(CASE
        WHEN accounts.id = transfers.source_account_id
            THEN transfers.amount * -1
        WHEN accounts.id = transfers.target_account_id
            THEN transfers.amount
        ELSE
            0.00
        END)
    AS balance
FROM
    users
JOIN accounts ON accounts.user_id = users.id
JOIN banks ON banks.id = accounts.bank_idq
LEFT JOIN transfers ON
    transfers.source_account_id = accounts.id
    OR transfers.target_account_id = accounts.id
GROUP BY users.name, banks.name
ORDER BY username ASC, balance ASC

EXPLAIN通过PgAdmin4执行查询，然后使用嵌套循环发送使用信息并与客户关联：

通信总成本为280k ，不含JOIN通信传输。您可以在媒体上查询7000 毫秒（7 秒）的总时间。

索引？

我们可以通过索引来解决问题。 Neste nosso exemplo, cheguei a criar o índice na tabela de Transfers (onde está acontecendo o Nested Loop)：

DROP INDEX IF EXISTS transfers_in, transfers_out;
CREATE INDEX transfers_in ON transfers (target_account_id);
CREATE INDEX transfers_out ON transfers (source_account_id);

...o que reduziu de 7000ms para 400ms (melhoria gigante), mas o índice tem um custo: a cada escrita na tabela transfer, o índice é atualizado.

好的，有什么特别的选择吗？

注意：在产品中的应用程序中，您可以使用索引。由于影响文字的原因，我们不滥用这些指标，因此我们将对其进行分析和使用。

Reduzindo 嵌套循环的复合体

Como já podemos saber，一种复杂的二次复杂算法（嵌套循环）的解析器形式，可减少线性、对数或常量。

嵌套卡索，analisamos que o melhor que podemos conseguir é reduzindo para Linear, or seja, percorrendo a tabela Transfers 1 ou 2 ou 3 vezes se for o caso, mas não de forma aninhada (nested)。

你有什么困难吗？ Criar uma 查询 com SELECTs e Sub-SELECTs？塔尔韦斯（Talvez）是一个询问巨大的问题和困难的问题。

CTE 的救援

通过CTE 的方式，我们可以组织大量的查询、大范围的查询以及多种查询，方便您理解信息。

在这个问题上，要进行一次多次的表演，首先要进行线性形式的查询（不是嵌套的）。

WITH 
accounts_idx AS(
    SELECT 
        accounts.id AS account_id,
        users.name AS username,
        banks.name AS bank
    FROM accounts
    JOIN users ON users.id = accounts.user_id
    JOIN banks ON banks.id = accounts.bank_id
),
accounts_from AS (
    SELECT 
        idx.username,
        idx.bank,
        SUM(transfers.amount * -1) AS balance
    FROM transfers
    JOIN accounts_idx idx ON idx.account_id = transfers.source_account_id
    GROUP BY idx.username, idx.bank
),
accounts_to AS (
    SELECT 
        idx.username,
        idx.bank,
        SUM(transfers.amount) AS balance
    FROM transfers
    JOIN accounts_idx idx ON idx.account_id = transfers.target_account_id
    GROUP BY idx.username, idx.bank
),
results AS (
    SELECT * FROM accounts_from
    UNION
    SELECT * FROM accounts_to
)
SELECT 
    username,
    bank,
    SUM(balance) AS balance
FROM results
GROUP BY username, bank
ORDER BY username ASC, balance ASC

WITH在 PostgreSQL中，执行查询时会出现 4 个临时问题：

• account_idx、guarda 信息 dos 用户 e bancos em uma 哈希
• account_from，根据 tabela“转账”buscando por saídas em contas
• account_to，percorre a tabela“转账”buscando por entradas em contas
• 结果, que faz a UNION das CTE's

这是线性代码的技术。 O 加入 com ORna tabela transfersfoi completamente removido！

作为CTE 的一个术语，它是一个 UNION 的缩写，它是一个真正的概念，它是一个 Quantidade de colunas、projetar como um Append、exatamente como na união de conjuntos。

分析 CTE 查询

Agora podemos novamente analisar com 说明没有 PgAdmin4：

可以修复嵌套循环的节点，并在 CTE 扫描中进行第一次扫描，以保证线性、后向聚合和查询过滤的数据。

Com isto，查询减少 7000ms 至 300ms！需要索引！

结论

避免使用 CTE 的缺点、实现材料化是必要的，并且需要进行空间规划，使用查询规划器 (EXPLAIN) 来解决模块化和性能滥用的问题！

继续链接到要点。