explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dOE5 : Optimization for: plan #sQkR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.896 15.452 ↑ 1.0 1,380 1

GroupAggregate (cost=2,120.70..2,209.51 rows=1,421 width=795) (actual time=13.432..15.452 rows=1,380 loops=1)

  • Group Key: f.financeiro_id, false, 0, e.nome, p.nome, p.email, p.razao_social, ((CASE (f.liquidacao IS NULL) WHEN CASE_TEST_EXPR THEN (c.descricao)::text ELSE NULL::text END)::character varying(50)), ve.nome, v.numero, cp.nome, p.codigo, (COALESCE(v_1.operacao_propria_id, f_1.operacao_propria_id)), (COALESCE((string_agg(DISTINCT (pr.nome)::text, ','::text)), (pro.nome)::text)), ((f.campos_adicionais)::text)
2. 1.085 13.556 ↓ 1.0 1,446 1

Sort (cost=2,120.70..2,124.25 rows=1,421 width=837) (actual time=13.416..13.556 rows=1,446 loops=1)

  • Sort Key: f.financeiro_id, e.nome, p.nome, p.email, p.razao_social, ((CASE (f.liquidacao IS NULL) WHEN CASE_TEST_EXPR THEN (c.descricao)::text ELSE NULL::text END)::character varying(50)), ve.nome, v.numero, cp.nome, p.codigo, (COALESCE(v_1.operacao_propria_id, f_1.operacao_propria_id)), (COALESCE((string_agg(DISTINCT (pr.nome)::text, ','::text)), (pro.nome)::text)), ((f.campos_adicionais)::text)
  • Sort Method: quicksort Memory: 794kB
3. 0.480 12.471 ↓ 1.0 1,446 1

Hash Left Join (cost=1,884.63..2,046.29 rows=1,421 width=837) (actual time=8.677..12.471 rows=1,446 loops=1)

  • Hash Cond: (f.financeiro_id = f_1.financeiro_id)
4. 0.441 9.521 ↓ 1.0 1,446 1

Hash Left Join (cost=1,562.23..1,693.76 rows=1,421 width=784) (actual time=6.188..9.521 rows=1,446 loops=1)

  • Hash Cond: (f.financeiro_id = cm.financeiro_id)
5. 0.388 8.634 ↑ 1.0 1,380 1

Hash Left Join (cost=1,530.87..1,634.59 rows=1,421 width=706) (actual time=5.722..8.634 rows=1,380 loops=1)

  • Hash Cond: (f.venda_id = v.id)
6. 0.519 7.853 ↑ 1.0 1,380 1

Hash Left Join (cost=1,422.45..1,522.44 rows=1,421 width=702) (actual time=5.326..7.853 rows=1,380 loops=1)

  • Hash Cond: (f.conta_id = c.conta_id)
7. 0.358 7.319 ↑ 1.0 1,380 1

Hash Left Join (cost=1,420.48..1,516.61 rows=1,421 width=584) (actual time=5.304..7.319 rows=1,380 loops=1)

  • Hash Cond: (p.vendedor_id = ve.pessoa_id)
8. 0.388 5.820 ↑ 1.0 1,380 1

Hash Left Join (cost=1,065.36..1,157.76 rows=1,421 width=579) (actual time=4.155..5.820 rows=1,380 loops=1)

  • Hash Cond: (f.cadastrado_por = cp.pessoa_id)
9. 0.496 4.525 ↑ 1.0 1,380 1

Hash Join (cost=710.24..798.91 rows=1,421 width=558) (actual time=3.242..4.525 rows=1,380 loops=1)

  • Hash Cond: (f.pessoa_id = p.pessoa_id)
10. 0.613 1.945 ↑ 1.0 1,380 1

Hash Join (cost=355.12..440.06 rows=1,421 width=464) (actual time=1.151..1.945 rows=1,380 loops=1)

  • Hash Cond: (f.empresa_id = e.pessoa_id)
11. 0.198 0.198 ↑ 1.0 1,380 1

Seq Scan on financeiro f (cost=0.00..81.21 rows=1,421 width=443) (actual time=0.007..0.198 rows=1,380 loops=1)

12. 0.507 1.134 ↑ 1.0 4,272 1

Hash (cost=301.72..301.72 rows=4,272 width=37) (actual time=1.134..1.134 rows=4,272 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 353kB
13. 0.627 0.627 ↑ 1.0 4,272 1

Seq Scan on pessoa e (cost=0.00..301.72 rows=4,272 width=37) (actual time=0.004..0.627 rows=4,272 loops=1)

14. 0.618 2.084 ↑ 1.0 4,272 1

Hash (cost=301.72..301.72 rows=4,272 width=110) (actual time=2.084..2.084 rows=4,272 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 483kB
15. 1.466 1.466 ↑ 1.0 4,272 1

Seq Scan on pessoa p (cost=0.00..301.72 rows=4,272 width=110) (actual time=0.007..1.466 rows=4,272 loops=1)

16. 0.470 0.907 ↑ 1.0 4,272 1

Hash (cost=301.72..301.72 rows=4,272 width=37) (actual time=0.907..0.907 rows=4,272 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 353kB
17. 0.437 0.437 ↑ 1.0 4,272 1

Seq Scan on pessoa cp (cost=0.00..301.72 rows=4,272 width=37) (actual time=0.004..0.437 rows=4,272 loops=1)

18. 0.552 1.141 ↑ 1.0 4,272 1

Hash (cost=301.72..301.72 rows=4,272 width=37) (actual time=1.141..1.141 rows=4,272 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 353kB
19. 0.589 0.589 ↑ 1.0 4,272 1

Seq Scan on pessoa ve (cost=0.00..301.72 rows=4,272 width=37) (actual time=0.003..0.589 rows=4,272 loops=1)

20. 0.005 0.015 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=134) (actual time=0.015..0.015 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
21. 0.010 0.010 ↑ 1.0 43 1

Seq Scan on conta c (cost=0.00..1.43 rows=43 width=134) (actual time=0.007..0.010 rows=43 loops=1)

22. 0.148 0.393 ↑ 1.0 1,663 1

Hash (cost=87.63..87.63 rows=1,663 width=20) (actual time=0.393..0.393 rows=1,663 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 101kB
23. 0.245 0.245 ↑ 1.0 1,663 1

Seq Scan on vendas v (cost=0.00..87.63 rows=1,663 width=20) (actual time=0.004..0.245 rows=1,663 loops=1)

24. 0.115 0.446 ↑ 1.0 650 1

Hash (cost=23.13..23.13 rows=659 width=94) (actual time=0.446..0.446 rows=650 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
25. 0.205 0.331 ↓ 1.0 660 1

Hash Left Join (cost=1.68..23.13 rows=659 width=94) (actual time=0.019..0.331 rows=660 loops=1)

  • Hash Cond: (cm.forma_pagamento_id = fp.forma_pagamento_id)
26. 0.116 0.116 ↓ 1.0 660 1

Seq Scan on conta_movimento cm (cost=0.00..19.59 rows=659 width=32) (actual time=0.006..0.116 rows=660 loops=1)

27. 0.004 0.010 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=94) (actual time=0.010..0.010 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
28. 0.006 0.006 ↑ 1.0 30 1

Seq Scan on forma_pagamento fp (cost=0.00..1.30 rows=30 width=94) (actual time=0.004..0.006 rows=30 loops=1)

29. 0.015 2.470 ↑ 20.2 70 1

Hash (cost=304.73..304.73 rows=1,414 width=64) (actual time=2.470..2.470 rows=70 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 23kB
30. 0.105 2.455 ↑ 20.2 70 1

Hash Left Join (cost=212.24..304.73 rows=1,414 width=64) (actual time=1.726..2.455 rows=70 loops=1)

  • Hash Cond: (f_1.financeiro_id = fi.financeiro_id)
  • Filter: (COALESCE((string_agg(DISTINCT (pr.nome)::text, ','::text)), (pro.nome)::text) IS NOT NULL)
  • Rows Removed by Filter: 1,310
31. 0.144 2.015 ↑ 1.0 1,380 1

Hash Left Join (cost=112.51..201.26 rows=1,421 width=52) (actual time=1.236..2.015 rows=1,380 loops=1)

  • Hash Cond: (COALESCE(v_1.operacao_propria_id, f_1.operacao_propria_id) = pro.id)
32. 0.488 1.846 ↑ 1.0 1,380 1

Hash Left Join (cost=108.42..193.36 rows=1,421 width=64) (actual time=1.205..1.846 rows=1,380 loops=1)

  • Hash Cond: (f_1.venda_id = v_1.id)
33. 0.183 0.183 ↑ 1.0 1,380 1

Seq Scan on financeiro f_1 (cost=0.00..81.21 rows=1,421 width=48) (actual time=0.018..0.183 rows=1,380 loops=1)

34. 0.199 1.175 ↑ 1.0 1,663 1

Hash (cost=87.63..87.63 rows=1,663 width=32) (actual time=1.175..1.175 rows=1,663 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 95kB
35. 0.976 0.976 ↑ 1.0 1,663 1

Seq Scan on vendas v_1 (cost=0.00..87.63 rows=1,663 width=32) (actual time=0.020..0.976 rows=1,663 loops=1)

36. 0.010 0.025 ↑ 1.0 93 1

Hash (cost=2.93..2.93 rows=93 width=36) (actual time=0.025..0.025 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
37. 0.015 0.015 ↑ 1.0 93 1

Seq Scan on produtos pro (cost=0.00..2.93 rows=93 width=36) (actual time=0.008..0.015 rows=93 loops=1)

38. 0.001 0.335 ↑ 63.0 4 1

Hash (cost=96.58..96.58 rows=252 width=48) (actual time=0.335..0.335 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.015 0.334 ↑ 63.0 4 1

GroupAggregate (cost=89.02..94.06 rows=252 width=48) (actual time=0.328..0.334 rows=4 loops=1)

  • Group Key: fi.financeiro_id
40. 0.005 0.319 ↑ 63.0 4 1

Sort (cost=89.02..89.65 rows=252 width=36) (actual time=0.318..0.319 rows=4 loops=1)

  • Sort Key: fi.financeiro_id
  • Sort Method: quicksort Memory: 25kB
41. 0.006 0.314 ↑ 63.0 4 1

Merge Join (cost=4.70..78.97 rows=252 width=36) (actual time=0.181..0.314 rows=4 loops=1)

  • Merge Cond: (vop.operacao_propria_id = pr.id)
42. 0.007 0.289 ↑ 246.4 5 1

Nested Loop (cost=1.11..4,348.29 rows=1,232 width=32) (actual time=0.158..0.289 rows=5 loops=1)

43. 0.027 0.196 ↑ 57.8 86 1

Nested Loop (cost=0.84..2,569.20 rows=4,972 width=32) (actual time=0.023..0.196 rows=86 loops=1)

44. 0.006 0.091 ↑ 45.8 39 1

Nested Loop (cost=0.56..1,440.58 rows=1,788 width=32) (actual time=0.017..0.091 rows=39 loops=1)

45. 0.017 0.017 ↑ 48.9 34 1

Index Scan using idx_vendas_operacao_propria_id on vendas vop (cost=0.28..301.36 rows=1,663 width=32) (actual time=0.009..0.017 rows=34 loops=1)

46. 0.068 0.068 ↑ 1.0 1 34

Index Scan using idx_vendas_produtos_vendas_id on vendas_produtos vpr (cost=0.28..0.68 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=34)

  • Index Cond: (vendas_id = vop.id)
47. 0.078 0.078 ↑ 1.5 2 39

Index Scan using idx_financeiro_item_vendas_produtos_id on financeiro_item fi (cost=0.28..0.60 rows=3 width=32) (actual time=0.002..0.002 rows=2 loops=39)

  • Index Cond: (vendas_produtos_id = vpr.vendas_produtos_id)
48. 0.086 0.086 ↓ 0.0 0 86

Index Scan using pk_financeiro on financeiro f2 (cost=0.28..0.36 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=86)

  • Index Cond: (financeiro_id = fi.financeiro_id)
  • Filter: ((fatura_numero IS NOT NULL) OR (tipo = ANY ('{fatura_fornecedor,avulso_fornecedor}'::financeiro_tipo[])))
  • Rows Removed by Filter: 0
49. 0.008 0.019 ↑ 1.1 17 1

Sort (cost=3.57..3.61 rows=19 width=36) (actual time=0.019..0.019 rows=17 loops=1)

  • Sort Key: pr.id
  • Sort Method: quicksort Memory: 26kB
50. 0.011 0.011 ↑ 1.0 19 1

Seq Scan on produtos pr (cost=0.00..3.16 rows=19 width=36) (actual time=0.008..0.011 rows=19 loops=1)

  • Filter: (tipo = 'OP'::bpchar)
  • Rows Removed by Filter: 74