优化PostgreSQL中的查询-耗时太长

Question

使用此视图查看所有"反社会者"在过去30天内将其状态变为301问题是查询大约需要30秒，我必须降低到最大20秒

CREATE OR REPLACE VIEW public.socios_desligados_inadimplencis_ultimos_30_dias_v2
AS SELECT tb.nome,
tb.cpf,
tb.email,
tb.plano,
tb.status_contrato,
CASE
WHEN ad.dt_inicial IS NULL THEN tb.dt_inicial
ELSE ad.dt_inicial
END AS data_admissao,
tb.dthr_desligamento,
tb.total_parcelas_pagas
FROM ( SELECT p.idpessoa,
p.nome,
p.cpf,
p.email,
pl.dscplano AS plano,
ea.dscestado_ativacao AS status_contrato,
eal.ins_dthr AS dthr_desligamento,
cr.valor_total AS total_parcelas_pagas,
con.dt_inicial
FROM base_pessoa p
JOIN ( SELECT gs_contrato.idpessoa,
gs_contrato.idcontrato,
gs_contrato.idplano,
gs_contrato.idestado_ativacao,
gs_contrato.dt_inicial
FROM gs_contrato
WHERE gs_contrato.idestado_ativacao::text = '301'::text AND gs_contrato.alt_dthr >= (CURRENT_DATE - 30)) con ON p.idpessoa::text = con.idpessoa::text
JOIN gs_plano pl ON con.idplano::text = pl.idplano::text
JOIN gs_estado_ativacao ea ON con.idestado_ativacao::text = ea.idestado_ativacao::text
JOIN ( SELECT row_number() OVER (PARTITION BY lg.idcontrato ORDER BY lg.ins_dthr DESC) AS linha,
lg.idcontrato,
lg.idestado_ativacao,
lg.ins_dthr
FROM gs_estado_ativacao_l lg
JOIN gs_contrato con_1 ON con_1.idcontrato::text = lg.idcontrato::text
WHERE lg.idestado_ativacao::text = '301'::text AND con_1.idestado_ativacao::text = '301'::text AND con_1.alt_dthr >= (CURRENT_DATE - 30) AND lg.ins_dthr >= (CURRENT_DATE - 30)) eal ON con.idcontrato::text = eal.idcontrato::text AND eal.linha = 1
JOIN ( SELECT ccr.idcontrato,
sum(ccr.valor - ccr.valor_desconto + ccr.valor_juros + ccr.valor_multa + ccr.valor_adesao + ccr.valor_acrescimo) AS valor_total
FROM cx_conta_receber ccr
JOIN gs_contrato c ON c.idcontrato::text = ccr.idcontrato::text
WHERE ccr.status::text = 'PA'::text AND c.idestado_ativacao::text = '301'::text AND c.alt_dthr >= (CURRENT_DATE - 30)
GROUP BY ccr.idcontrato) cr ON cr.idcontrato::text = con.idcontrato::text) tb
JOIN data_adesao ad ON tb.idpessoa::text = ad.idpessoa::text;

Answer 1

我检查了您的查询。我写了一些关于表现的建议。

1. 建议使用物化CTE(with as materialized(而不是子查询
2. 也许您使用了子查询或表联接来获得一些值。此时可以使用返回表函数
3. 检查索引。字段用于条件(where或join(，在order by或group by命令上必须索引
4. 不建议使用强制转换字段或将函数设置为条件中的字段。此时DB不能使用索引。如果需要将函数设置为条件中的字段，则建议创建expression index(function-based index(

您的查询有一些强制类型：

WHERE gs_contrato.idestado_ativacao::text = '301'::text 
AND gs_contrato.alt_dthr >= (CURRENT_DATE - 30)) con ON 
p.idpessoa::text = con.idpessoa::text
JOIN gs_plano pl 
ON con.idplano::text = pl.idplano::text
JOIN gs_estado_ativacao ea 
ON con.idestado_ativacao::text = ea.idestado_ativacao::text
WHERE lg.idestado_ativacao::text = '301'::text 
AND con_1.idestado_ativacao::text = '301'::text 
AND con_1.alt_dthr >= (CURRENT_DATE - 30) 
AND lg.ins_dthr >= (CURRENT_DATE - 30)) eal ON con.idcontrato::text = eal.idcontrato::text AND eal.linha = 1

如果需要强制转换类型，请在创建索引时使用相同的强制转换类型。表达式索引。

最新也是最重要的建议是使用explain analyze进行此查询以获得DB的执行计划。