如何优化查询Postgres

小编典典

如何优化查询Postgres

sql

我正在运行以下查询：

SELECT fat.*   
FROM Table1 fat  
LEFT JOIN modo_captura mc       ON mc.id = fat.modo_captura_id  
INNER JOIN loja lj              ON lj.id = fat.loja_id  
INNER JOIN rede rd              ON rd.id = fat.rede_id  
INNER JOIN bandeira bd          ON bd.id = fat.bandeira_id  
INNER JOIN produto pd           ON pd.id = fat.produto_id  
INNER JOIN loja_extensao le     ON le.id = fat.loja_extensao_id  
INNER JOIN conta ct             ON ct.id = fat.conta_id
INNER JOIN banco bc             ON bc.id = ct.banco_id  
LEFT JOIN conciliacao_vendas cv ON fat.empresa_id = cv.empresa_id AND cv.chavefato = fat.chavefato AND fat.rede_id = cv.rede_id  
WHERE 1 = 1  
AND cv.controle_upload_arquivo_id = 6906  
AND fat.parcela = 1  
ORDER BY fat.data_venda, fat.data_credito limit 20

但是很慢。这里是解释计划：http :
//explain.depesz.com/s/DnXH

阅读 142

2021-04-15

共1个答案

小编典典

尝试以下重写版本：

SELECT fat.*   
FROM   Table1 fat
JOIN   conciliacao_vendas cv USING (empresa_id, chavefato, rede_id)
JOIN   loja lj               ON lj.id = fat.loja_id  
JOIN   rede rd               ON rd.id = fat.rede_id  
JOIN   bandeira bd           ON bd.id = fat.bandeira_id  
JOIN   produto pd            ON pd.id = fat.produto_id  
JOIN   loja_extensao le      ON le.id = fat.loja_extensao_id  
JOIN   conta ct              ON ct.id = fat.conta_id
JOIN   banco bc              ON bc.id = ct.banco_id
LEFT   JOIN modo_captura mc  ON mc.id = fat.modo_captura_id  
WHERE  cv.controle_upload_arquivo_id = 6906  
AND    fat.parcela = 1  
ORDER  BY fat.data_venda, fat.data_credito
LIMIT  20;

JOIN语法和连接顺序

特别是，我将 误导性LEFT JOIN修正为conciliacao_vendas，无论如何，后者[INNER] JOIN在后来的WHERE条件下都被迫充当普通人。这将简化查询计划，并允许在过程的早期消除行，这将使所有内容便宜很多。相关答案及详细说明：

详细解释JOIN与LEFT JOIN和WHERE条件表现建议

USING 只是语法上的简写。

由于查询中涉及许多表，并且重写查询联接表的顺序现在是最佳的，因此您可以对此进行微调，SET LOCAL join_collapse_limit = 1以节省计划开销并避免劣质的查询计划。在 单个事务中 运行：

BEGIN;
SET LOCAL join_collapse_limit = 1;
SELECT ...;  -- read data here
COMMIT;      -- or ROOLBACK;

指数

在具有很多行的查询表上添加一些索引（不需要几十个），尤其是（从查询计划中获取）：

对public.conta ct进行序列扫描… 行= 6771
在public.loja lj上进行序列扫描… 行= 1568
对public.loja_extensao le上进行序列扫描… 行= 16394

这特别奇怪，因为这些列看起来像 主键列 ，应该已经有一个索引…

所以：

CREATE INDEX conta_pkey_idx ON public.conta (id);
CREATE INDEX loja_pkey_idx ON public.loja (id);
CREATE INDEX loja_extensao_pkey_idx ON public.loja_extensao (id);

为了使它真正发胖， 多列索引 将很有用：

CREATE INDEX foo ON Table1 (parcela, data_venda, data_credito);

2021-04-15