explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b2M1

Settings
# exclusive inclusive rows x rows loops node
1. 202.106 18,653.216 ↓ 2.0 24,332 1

WindowAgg (cost=6,496,505.18..6,498,050.99 rows=12,115 width=901) (actual time=10,522.526..18,653.216 rows=24,332 loops=1)

2.          

CTE tiporecebimento

3. 0.099 0.099 ↑ 1.0 22 1

Index Only Scan using idxsyslookupgrupointkey on sys_lookup (cost=0.28..14.39 rows=22 width=22) (actual time=0.052..0.099 rows=22 loops=1)

  • Index Cond: (grupo = 'TipoRecebimento'::text)
  • Heap Fetches: 22
4.          

CTE codigoproduto

5. 0.030 0.078 ↑ 1.0 17 1

Hash Join (cost=1.20..2.44 rows=17 width=16) (actual time=0.068..0.078 rows=17 loops=1)

  • Hash Cond: ((pr.empresaproduto_id)::integer = (pe.empresaproduto_id)::integer)
6. 0.024 0.024 ↑ 1.0 17 1

Seq Scan on rel_produtorecebimento pr (cost=0.00..1.17 rows=17 width=11) (actual time=0.022..0.024 rows=17 loops=1)

7. 0.015 0.024 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=13) (actual time=0.023..0.024 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.009 0.009 ↑ 1.0 9 1

Seq Scan on cad_empresaproduto pe (cost=0.00..1.09 rows=9 width=13) (actual time=0.007..0.009 rows=9 loops=1)

9.          

CTE titulos

10. 44.242 17,930.695 ↓ 1.2 14,378 1

Nested Loop (cost=221,351.06..6,480,284.79 rows=12,115 width=512) (actual time=10,118.792..17,930.695 rows=14,378 loops=1)

11. 35.315 10,956.257 ↓ 1.2 14,378 1

Nested Loop Left Join (cost=221,350.80..6,476,650.29 rows=12,115 width=442) (actual time=10,117.581..10,956.257 rows=14,378 loops=1)

12. 15.309 10,834.674 ↓ 1.2 14,378 1

Hash Join (cost=221,350.38..6,468,983.36 rows=12,115 width=316) (actual time=10,117.544..10,834.674 rows=14,378 loops=1)

  • Hash Cond: ((r.tiporecebimento)::smallint = (tr.id)::integer)
13. 178.068 10,819.247 ↓ 1.2 14,378 1

Hash Join (cost=221,349.67..6,468,815.86 rows=12,169 width=284) (actual time=10,117.413..10,819.247 rows=14,378 loops=1)

  • Hash Cond: ((rb.receberbaixa_id)::integer = (frb.receberbaixa_id)::integer)
14. 175.558 10,568.451 ↑ 1.0 339,108 1

Hash Left Join (cost=211,578.88..6,458,146.32 rows=342,377 width=270) (actual time=1,062.588..10,568.451 rows=339,108 loops=1)

  • Hash Cond: (((c.codigoproduto)::text = (cp.codigo)::text) AND ((r.tiporecebimento)::smallint = (cp.tiporecebimento)::smallint))
15. 222.954 10,392.782 ↑ 1.0 339,108 1

Nested Loop (cost=211,578.28..6,455,440.25 rows=342,377 width=244) (actual time=1,062.457..10,392.782 rows=339,108 loops=1)

16. 418.492 2,031.236 ↑ 1.0 339,108 1

Hash Join (cost=211,560.11..222,756.60 rows=342,377 width=240) (actual time=1,062.405..2,031.236 rows=339,108 loops=1)

  • Hash Cond: ((r.contrato_id)::integer = (c.contrato_id)::integer)
17. 454.742 1,036.243 ↑ 1.0 342,279 1

Hash Join (cost=30,663.15..37,167.09 rows=342,411 width=30) (actual time=485.806..1,036.243 rows=342,279 loops=1)

  • Hash Cond: ((rb.receber_id)::integer = (r.receber_id)::integer)
18. 97.127 97.127 ↑ 1.0 342,279 1

Seq Scan on rel_receberbaixa rb (cost=0.00..5,605.11 rows=342,411 width=8) (actual time=0.010..97.127 rows=342,279 loops=1)

19. 250.989 484.374 ↑ 1.0 726,364 1

Hash (cost=21,577.51..21,577.51 rows=726,851 width=26) (actual time=484.374..484.374 rows=726,364 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 50734kB
20. 233.385 233.385 ↑ 1.0 726,364 1

Seq Scan on fin_receber r (cost=0.00..21,577.51 rows=726,851 width=26) (actual time=0.013..233.385 rows=726,364 loops=1)

21. 28.381 576.501 ↑ 1.0 33,408 1

Hash (cost=180,478.51..180,478.51 rows=33,476 width=214) (actual time=576.500..576.501 rows=33,408 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6726kB
22. 14.968 548.120 ↑ 1.0 33,408 1

Hash Join (cost=7.34..180,478.51 rows=33,476 width=214) (actual time=0.150..548.120 rows=33,408 loops=1)

  • Hash Cond: ((c.curso_id)::integer = (cc.curso_id)::integer)
23. 42.656 533.135 ↑ 1.0 33,410 1

Merge Join (cost=5.89..180,370.56 rows=33,476 width=202) (actual time=0.125..533.135 rows=33,410 loops=1)

  • Merge Cond: ((c.pessoa_id)::integer = (p.pessoa_id)::integer)
24. 44.091 360.466 ↑ 1.0 33,411 1

Nested Loop (cost=5.16..170,775.93 rows=33,479 width=76) (actual time=0.094..360.466 rows=33,411 loops=1)

25. 115.909 115.909 ↑ 1.0 33,411 1

Index Scan using idxcontratopessoa on mov_contrato c (cost=0.29..6,617.48 rows=33,479 width=44) (actual time=0.016..115.909 rows=33,411 loops=1)

26. 33.411 200.466 ↑ 1.0 1 33,411

Aggregate (cost=4.87..4.88 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=33,411)

27. 20.235 167.055 ↓ 0.0 0 33,411

Nested Loop (cost=0.56..4.87 rows=1 width=13) (actual time=0.005..0.005 rows=0 loops=33,411)

28. 133.644 133.644 ↓ 0.0 0 33,411

Index Scan using idxalunoturmacontrato on rel_alunoturma a (cost=0.29..2.57 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=33,411)

  • Index Cond: ((contrato_id)::integer = (c.contrato_id)::integer)
  • Filter: ((alunoativo)::smallint = 1)
  • Rows Removed by Filter: 2
29. 13.176 13.176 ↑ 1.0 1 6,588

Index Scan using pkturma on col_turma t_2 (cost=0.28..2.29 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=6,588)

  • Index Cond: ((turma_id)::integer = (a.turma_id)::integer)
30. 130.013 130.013 ↓ 1.0 150,762 1

Index Scan using pkpessoa on cad_pessoa p (cost=0.42..8,801.84 rows=150,761 width=130) (actual time=0.015..130.013 rows=150,762 loops=1)

31. 0.006 0.017 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=20) (actual time=0.016..0.017 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.011 0.011 ↑ 1.0 20 1

Seq Scan on col_curso cc (cost=0.00..1.20 rows=20 width=20) (actual time=0.008..0.011 rows=20 loops=1)

33. 678.216 8,138.592 ↑ 1.0 1 339,108

Aggregate (cost=18.17..18.18 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=339,108)

34. 7,460.376 7,460.376 ↓ 20.0 20 339,108

Index Scan using idxrecebercontrato on fin_receber rr (cost=0.42..18.17 rows=1 width=4) (actual time=0.008..0.022 rows=20 loops=339,108)

  • Index Cond: ((contrato_id)::integer = (r.contrato_id)::integer)
  • Filter: ((tiporecebimento)::smallint = (r.tiporecebimento)::smallint)
  • Rows Removed by Filter: 5
35. 0.020 0.111 ↑ 1.0 17 1

Hash (cost=0.34..0.34 rows=17 width=66) (actual time=0.111..0.111 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.091 0.091 ↑ 1.0 17 1

CTE Scan on codigoproduto cp (cost=0.00..0.34 rows=17 width=66) (actual time=0.072..0.091 rows=17 loops=1)

37. 2.861 72.728 ↓ 1.2 14,386 1

Hash (cost=9,618.90..9,618.90 rows=12,151 width=18) (actual time=72.728..72.728 rows=14,386 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 896kB
38. 69.867 69.867 ↓ 1.2 14,386 1

Seq Scan on fin_receberbaixa frb (cost=0.00..9,618.90 rows=12,151 width=18) (actual time=0.013..69.867 rows=14,386 loops=1)

  • Filter: (((lancamento)::timestamp without time zone >= '2019-01-01 00:00:00'::timestamp without time zone) AND ((lancamento)::timestamp without time zone <= '2019-08-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 327423
39. 0.007 0.118 ↑ 1.0 22 1

Hash (cost=0.44..0.44 rows=22 width=36) (actual time=0.118..0.118 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.111 0.111 ↑ 1.0 22 1

CTE Scan on tiporecebimento tr (cost=0.00..0.44 rows=22 width=36) (actual time=0.055..0.111 rows=22 loops=1)

41. 86.268 86.268 ↑ 1.0 1 14,378

Index Scan using pkpessoa on cad_pessoa t_1 (cost=0.42..0.63 rows=1 width=130) (actual time=0.006..0.006 rows=1 loops=14,378)

  • Index Cond: ((pessoa_id)::integer = (c.titular_id)::integer)
42. 14.378 6,930.196 ↑ 1.0 1 14,378

Result (cost=0.26..0.27 rows=1 width=32) (actual time=0.482..0.482 rows=1 loops=14,378)

43.          

Initplan (forResult)

44. 6,915.818 6,915.818 ↑ 1.0 1 14,378

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.481..0.481 rows=1 loops=14,378)

45. 57.905 18,451.110 ↓ 2.0 24,332 1

Hash Left Join (cost=16,203.56..16,568.16 rows=12,115 width=806) (actual time=10,522.493..18,451.110 rows=24,332 loops=1)

  • Hash Cond: ((t.origem = (od.origem)::integer) AND ((t.origem_id)::integer = (od.origem_id)::integer))
46. 17,990.209 17,990.209 ↓ 1.2 14,378 1

CTE Scan on titulos t (cost=0.00..242.30 rows=12,115 width=646) (actual time=10,118.800..17,990.209 rows=14,378 loops=1)

47. 45.271 402.996 ↑ 1.0 75,487 1

Hash (cost=15,071.09..15,071.09 rows=75,498 width=168) (actual time=402.996..402.996 rows=75,487 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16661kB
48. 50.627 357.725 ↑ 1.0 75,487 1

Hash Join (cost=13,562.76..15,071.09 rows=75,498 width=168) (actual time=296.855..357.725 rows=75,487 loops=1)

  • Hash Cond: ((od.documentofiscal_id)::integer = (fdf.documentofiscal_id)::integer)
49. 11.138 11.138 ↑ 1.0 75,748 1

Seq Scan on rel_origemdfe od (cost=0.00..1,309.48 rows=75,748 width=12) (actual time=0.016..11.138 rows=75,748 loops=1)

50. 64.855 295.960 ↑ 1.0 75,506 1

Hash (cost=12,618.80..12,618.80 rows=75,517 width=160) (actual time=295.960..295.960 rows=75,506 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 15933kB
51. 231.105 231.105 ↑ 1.0 75,506 1

Seq Scan on fin_documentofiscal fdf (cost=0.00..12,618.80 rows=75,517 width=160) (actual time=0.045..231.105 rows=75,506 loops=1)

  • Filter: ((status)::integer <> ALL ('{3,4,5}'::integer[]))
  • Rows Removed by Filter: 261
Planning time : 4.899 ms
Execution time : 18,659.671 ms