explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rIlb : Optimization for: plan #sQkR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.822 16.682 ↑ 1.0 1,380 1

Hash Right Join (cost=1,973.76..2,099.90 rows=1,421 width=981) (actual time=15.027..16.682 rows=1,380 loops=1)

  • Hash Cond: (f_1.financeiro_id = f.financeiro_id)
2.          

CTE movimentacoes

3. 0.333 0.599 ↑ 2.1 279 1

GroupAggregate (cost=53.98..66.21 rows=583 width=48) (actual time=0.258..0.599 rows=279 loops=1)

  • Group Key: cm.financeiro_id
4. 0.103 0.266 ↑ 1.9 346 1

Sort (cost=53.98..55.63 rows=659 width=94) (actual time=0.242..0.266 rows=346 loops=1)

  • Sort Key: cm.financeiro_id
  • Sort Method: quicksort Memory: 52kB
5. 0.073 0.163 ↑ 1.9 346 1

Hash Join (cost=1.68..23.13 rows=659 width=94) (actual time=0.035..0.163 rows=346 loops=1)

  • Hash Cond: (cm.forma_pagamento_id = fp.forma_pagamento_id)
6. 0.075 0.075 ↓ 1.0 660 1

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

7. 0.006 0.015 ↑ 1.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
8. 0.009 0.009 ↑ 1.0 30 1

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

9. 0.116 2.592 ↑ 20.2 70 1

Hash Left Join (cost=212.24..304.73 rows=1,414 width=64) (actual time=1.748..2.592 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
10. 0.152 2.034 ↑ 1.0 1,380 1

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

  • Hash Cond: (COALESCE(v_1.operacao_propria_id, f_1.operacao_propria_id) = pro.id)
11. 0.556 1.854 ↑ 1.0 1,380 1

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

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

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

13. 0.183 1.061 ↑ 1.0 1,663 1

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

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

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

15. 0.011 0.028 ↑ 1.0 93 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
16. 0.017 0.017 ↑ 1.0 93 1

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

17. 0.003 0.442 ↑ 63.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.016 0.439 ↑ 63.0 4 1

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

  • Group Key: fi.financeiro_id
19. 0.005 0.423 ↑ 63.0 4 1

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

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

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

  • Merge Cond: (vop.operacao_propria_id = pr.id)
21. 0.025 0.389 ↑ 246.4 5 1

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

22. 0.006 0.278 ↑ 57.8 86 1

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

23. 0.027 0.116 ↑ 45.8 39 1

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

24. 0.021 0.021 ↑ 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.021 rows=34 loops=1)

25. 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)
26. 0.156 0.156 ↑ 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.004 rows=2 loops=39)

  • Index Cond: (vendas_produtos_id = vpr.vendas_produtos_id)
27. 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
28. 0.010 0.023 ↑ 1.1 17 1

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

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

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

  • Filter: (tipo = 'OP'::bpchar)
  • Rows Removed by Filter: 74
30. 0.655 13.268 ↑ 1.0 1,380 1

Hash (cost=1,677.54..1,677.54 rows=1,421 width=816) (actual time=13.268..13.268 rows=1,380 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 492kB
31. 0.745 12.613 ↑ 1.0 1,380 1

Hash Right Join (cost=1,657.87..1,677.54 rows=1,421 width=816) (actual time=11.460..12.613 rows=1,380 loops=1)

  • Hash Cond: (m.financeiro_id = f.financeiro_id)
32. 0.675 0.675 ↑ 2.1 279 1

CTE Scan on movimentacoes m (cost=0.00..11.66 rows=583 width=48) (actual time=0.261..0.675 rows=279 loops=1)

33. 1.016 11.193 ↑ 1.0 1,380 1

Hash (cost=1,640.11..1,640.11 rows=1,421 width=784) (actual time=11.193..11.193 rows=1,380 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 489kB
34. 0.690 10.177 ↑ 1.0 1,380 1

Hash Left Join (cost=1,532.54..1,640.11 rows=1,421 width=784) (actual time=5.893..10.177 rows=1,380 loops=1)

  • Hash Cond: (f.venda_id = v.id)
35. 0.437 8.766 ↑ 1.0 1,380 1

Hash Left Join (cost=1,424.12..1,527.96 rows=1,421 width=780) (actual time=5.166..8.766 rows=1,380 loops=1)

  • Hash Cond: (f.conta_id = c.conta_id)
36. 0.494 8.304 ↑ 1.0 1,380 1

Hash Left Join (cost=1,422.16..1,522.13 rows=1,421 width=662) (actual time=5.129..8.304 rows=1,380 loops=1)

  • Hash Cond: (p.vendedor_id = ve.pessoa_id)
37. 0.477 6.620 ↑ 1.0 1,380 1

Hash Left Join (cost=1,067.04..1,163.28 rows=1,421 width=657) (actual time=3.929..6.620 rows=1,380 loops=1)

  • Hash Cond: (f.cadastrado_por = cp.pessoa_id)
38. 0.399 5.251 ↑ 1.0 1,380 1

Hash Left Join (cost=711.91..804.43 rows=1,421 width=636) (actual time=3.031..5.251 rows=1,380 loops=1)

  • Hash Cond: (f.forma_pagamento_id = ffp.forma_pagamento_id)
39. 0.595 4.841 ↑ 1.0 1,380 1

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

  • Hash Cond: (f.pessoa_id = p.pessoa_id)
40. 0.857 2.294 ↑ 1.0 1,380 1

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

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

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

42. 0.502 1.040 ↑ 1.0 4,272 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 353kB
43. 0.538 0.538 ↑ 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.538 rows=4,272 loops=1)

44. 0.617 1.952 ↑ 1.0 4,272 1

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

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

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

46. 0.004 0.011 ↑ 1.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
47. 0.007 0.007 ↑ 1.0 30 1

Seq Scan on forma_pagamento ffp (cost=0.00..1.30 rows=30 width=94) (actual time=0.005..0.007 rows=30 loops=1)

48. 0.454 0.892 ↑ 1.0 4,272 1

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

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

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

50. 0.540 1.190 ↑ 1.0 4,272 1

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

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

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

52. 0.006 0.025 ↑ 1.0 43 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
53. 0.019 0.019 ↑ 1.0 43 1

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

54. 0.245 0.721 ↑ 1.0 1,663 1

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

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

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