explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d45E

Settings
# exclusive inclusive rows x rows loops node
1. 23.136 30,345.239 ↑ 136.7 944 1

WindowAgg (cost=982,130.74..1,335,888.63 rows=129,030 width=883) (actual time=642.821..30,345.239 rows=944 loops=1)

2.          

CTE tiporecebimento

3. 0.089 0.089 ↑ 1.0 20 1

Index Only Scan using idxsyslookupgrupointkey on sys_lookup (cost=0.27..7.58 rows=20 width=19) (actual time=0.043..0.089 rows=20 loops=1)

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

CTE codigoproduto

5. 0.001 0.008 ↓ 0.0 0 1

Hash Join (cost=26.20..45.17 rows=710 width=66) (actual time=0.008..0.008 rows=0 loops=1)

  • Hash Cond: ((pr.empresaproduto_id)::integer = (pe.empresaproduto_id)::integer)
6. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on rel_produtorecebimento pr (cost=0.00..17.10 rows=710 width=38) (actual time=0.007..0.007 rows=0 loops=1)

7. 0.000 0.000 ↓ 0.0 0

Hash (cost=17.20..17.20 rows=720 width=36) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Seq Scan on cad_empresaproduto pe (cost=0.00..17.20 rows=720 width=36) (never executed)

9.          

CTE titulos

10. 6.928 3,305.909 ↓ 14.1 944 1

Nested Loop Left Join (cost=93,295.49..982,077.07 rows=67 width=476) (actual time=597.853..3,305.909 rows=944 loops=1)

11. 35.330 3,288.597 ↓ 14.1 944 1

Hash Join (cost=93,295.20..982,036.31 rows=67 width=310) (actual time=597.835..3,288.597 rows=944 loops=1)

  • Hash Cond: ((rb.receberbaixa_id)::integer = (frb.receberbaixa_id)::integer)
12. 53.206 3,250.761 ↓ 1.0 113,938 1

Nested Loop (cost=91,630.97..980,073.17 rows=113,868 width=296) (actual time=590.403..3,250.761 rows=113,938 loops=1)

13. 77.561 690.919 ↓ 1.0 113,938 1

Merge Left Join (cost=91,623.21..92,483.45 rows=113,868 width=292) (actual time=590.334..690.919 rows=113,938 loops=1)

  • Merge Cond: (((c.codigoproduto)::text = (cp.codigo)::text) AND ((r.tiporecebimento)::smallint = (cp.tiporecebimento)::smallint))
14. 111.127 613.338 ↓ 1.0 113,938 1

Sort (cost=91,575.39..91,860.06 rows=113,868 width=261) (actual time=590.306..613.338 rows=113,938 loops=1)

  • Sort Key: c.codigoproduto, r.tiporecebimento
  • Sort Method: quicksort Memory: 45364kB
15. 52.874 502.211 ↓ 1.0 113,938 1

Hash Join (cost=70,072.96..82,012.18 rows=113,868 width=261) (actual time=245.407..502.211 rows=113,938 loops=1)

  • Hash Cond: ((r.contrato_id)::integer = (c.contrato_id)::integer)
16. 129.756 246.211 ↓ 1.0 113,938 1

Hash Join (cost=3,288.03..13,661.57 rows=113,868 width=30) (actual time=42.246..246.211 rows=113,938 loops=1)

  • Hash Cond: ((r.receber_id)::integer = (rb.receber_id)::integer)
17. 74.438 74.438 ↑ 1.0 265,575 1

Seq Scan on fin_receber r (cost=0.00..8,221.44 rows=270,244 width=26) (actual time=0.006..74.438 rows=265,575 loops=1)

18. 19.871 42.017 ↓ 1.0 113,938 1

Hash (cost=1,864.68..1,864.68 rows=113,868 width=8) (actual time=42.017..42.017 rows=113,938 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5475kB
19. 22.146 22.146 ↓ 1.0 113,938 1

Seq Scan on rel_receberbaixa rb (cost=0.00..1,864.68 rows=113,868 width=8) (actual time=0.008..22.146 rows=113,938 loops=1)

20. 7.408 203.126 ↑ 1.0 11,683 1

Hash (cost=66,638.79..66,638.79 rows=11,691 width=235) (actual time=203.126..203.126 rows=11,683 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2738kB
21. 4.916 195.718 ↑ 1.0 11,683 1

Hash Join (cost=8.58..66,638.79 rows=11,691 width=235) (actual time=0.321..195.718 rows=11,683 loops=1)

  • Hash Cond: ((c.curso_id)::integer = (cc.curso_id)::integer)
22. 14.993 190.782 ↑ 1.0 11,683 1

Merge Join (cost=6.97..66,601.68 rows=11,691 width=203) (actual time=0.284..190.782 rows=11,683 loops=1)

  • Merge Cond: ((c.pessoa_id)::integer = (p.pessoa_id)::integer)
23. 11.488 122.800 ↑ 1.0 11,683 1

Nested Loop (cost=5.34..61,845.97 rows=11,691 width=71) (actual time=0.168..122.800 rows=11,683 loops=1)

24. 29.531 29.531 ↑ 1.0 11,683 1

Index Scan using idxcontratopessoa on mov_contrato c (cost=0.29..2,429.65 rows=11,691 width=39) (actual time=0.015..29.531 rows=11,683 loops=1)

25. 11.683 81.781 ↑ 1.0 1 11,683

Aggregate (cost=5.05..5.06 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=11,683)

26. 0.000 70.098 ↑ 1.0 1 11,683

Nested Loop (cost=0.56..5.05 rows=1 width=23) (actual time=0.006..0.006 rows=1 loops=11,683)

27. 58.415 58.415 ↑ 1.0 1 11,683

Index Scan using idxalunoturmacontrato on rel_alunoturma a (cost=0.29..2.75 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=11,683)

  • Index Cond: ((contrato_id)::integer = (c.contrato_id)::integer)
  • Filter: ((alunoativo)::smallint = 1)
  • Rows Removed by Filter: 3
28. 17.310 17.310 ↑ 1.0 1 8,655

Index Scan using pkturma on col_turma t_2 (cost=0.28..2.29 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=8,655)

  • Index Cond: ((turma_id)::integer = (a.turma_id)::integer)
29. 52.989 52.989 ↑ 1.0 79,662 1

Index Scan using pkpessoa on cad_pessoa p (cost=0.29..4,653.76 rows=79,698 width=136) (actual time=0.012..52.989 rows=79,662 loops=1)

30. 0.007 0.020 ↑ 1.0 27 1

Hash (cost=1.27..1.27 rows=27 width=40) (actual time=0.020..0.020 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 0.013 0.013 ↑ 1.0 27 1

Seq Scan on col_curso cc (cost=0.00..1.27 rows=27 width=40) (actual time=0.008..0.013 rows=27 loops=1)

32. 0.010 0.020 ↓ 0.0 0 1

Sort (cost=47.82..49.60 rows=710 width=66) (actual time=0.020..0.020 rows=0 loops=1)

  • Sort Key: cp.codigo, cp.tiporecebimento
  • Sort Method: quicksort Memory: 25kB
33. 0.010 0.010 ↓ 0.0 0 1

CTE Scan on codigoproduto cp (cost=0.00..14.20 rows=710 width=66) (actual time=0.010..0.010 rows=0 loops=1)

34. 341.814 2,506.636 ↑ 1.0 1 113,938

Aggregate (cost=7.76..7.77 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=113,938)

35. 2,164.822 2,164.822 ↓ 10.5 21 113,938

Index Scan using idxrecebercontrato on fin_receber rr (cost=0.42..7.76 rows=2 width=4) (actual time=0.005..0.019 rows=21 loops=113,938)

  • Index Cond: ((contrato_id)::integer = (r.contrato_id)::integer)
  • Filter: ((tiporecebimento)::smallint = (r.tiporecebimento)::smallint)
  • Rows Removed by Filter: 3
36. 0.161 2.506 ↓ 14.1 944 1

Hash (cost=1,663.38..1,663.38 rows=67 width=18) (actual time=2.506..2.506 rows=944 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
37. 2.301 2.345 ↓ 14.1 944 1

Bitmap Heap Scan on fin_receberbaixa frb (cost=3.05..1,663.38 rows=67 width=18) (actual time=2.027..2.345 rows=944 loops=1)

  • Recheck Cond: (((lancamento)::timestamp without time zone >= '2019-08-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 Index Recheck: 7303
  • Heap Blocks: lossy=110
38. 0.044 0.044 ↑ 7.4 1,280 1

Bitmap Index Scan on idxreceberbaixalancamento (cost=0.00..3.03 rows=9,489 width=0) (actual time=0.044..0.044 rows=1,280 loops=1)

  • Index Cond: (((lancamento)::timestamp without time zone >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((lancamento)::timestamp without time zone <= '2019-08-31 23:59:59'::timestamp without time zone))
39. 10.384 10.384 ↑ 1.0 1 944

Index Scan using pkpessoa on cad_pessoa t_1 (cost=0.29..0.61 rows=1 width=136) (actual time=0.011..0.011 rows=1 loops=944)

  • Index Cond: ((pessoa_id)::integer = (c.titular_id)::integer)
40. 0.000 30,322.103 ↑ 136.7 944 1

Nested Loop Left Join (cost=0.92..340,533.23 rows=129,030 width=772) (actual time=642.793..30,322.103 rows=944 loops=1)

  • Join Filter: (SubPlan 4)
  • Rows Removed by Join Filter: 12156332
41. 4.336 3,931.273 ↓ 47.2 944 1

Nested Loop (cost=0.92..8.34 rows=20 width=646) (actual time=598.803..3,931.273 rows=944 loops=1)

42. 4.429 3,315.225 ↓ 47.2 944 1

Hash Join (cost=0.65..2.44 rows=20 width=650) (actual time=597.975..3,315.225 rows=944 loops=1)

  • Hash Cond: ((t.tiporecebimento_id)::smallint = (tr.id)::integer)
43. 3,310.692 3,310.692 ↓ 14.1 944 1

CTE Scan on titulos t (cost=0.00..1.34 rows=67 width=620) (actual time=597.858..3,310.692 rows=944 loops=1)

44. 0.006 0.104 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=36) (actual time=0.104..0.104 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
45. 0.098 0.098 ↑ 1.0 20 1

CTE Scan on tiporecebimento tr (cost=0.00..0.40 rows=20 width=36) (actual time=0.045..0.098 rows=20 loops=1)

46. 2.832 611.712 ↑ 1.0 1 944

Result (cost=0.27..0.28 rows=1 width=32) (actual time=0.647..0.648 rows=1 loops=944)

47.          

Initplan (forResult)

48. 608.880 608.880 ↑ 1.0 1 944

Result (cost=0.00..0.27 rows=1 width=32) (actual time=0.644..0.645 rows=1 loops=944)

49. 2,223.492 2,246.720 ↑ 1.0 12,878 944

Materialize (cost=0.00..2,175.97 rows=12,903 width=126) (actual time=0.001..2.380 rows=12,878 loops=944)

50. 23.228 23.228 ↑ 1.0 12,878 1

Seq Scan on fin_documentofiscal df (cost=0.00..2,111.46 rows=12,903 width=126) (actual time=0.019..23.228 rows=12,878 loops=1)

  • Filter: ((status)::integer <> ALL ('{3,4,5}'::integer[]))
  • Rows Removed by Filter: 3
51.          

SubPlan (forNested Loop Left Join)

52. 24,313.664 24,313.664 ↑ 1.0 1 12,156,832

Index Only Scan using idxorigemdfe on rel_origemdfe od (cost=0.29..2.30 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=12,156,832)

  • Index Cond: ((origem = t.origem) AND (origem_id = (t.origem_id)::integer))
  • Heap Fetches: 6439000
Planning time : 4.898 ms
Execution time : 30,349.322 ms