explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kkKY

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 481,777.625 ↑ 1.0 1 1

Limit (cost=208,297.17..208,297.24 rows=1 width=233) (actual time=481,777.607..481,777.625 rows=1 loops=1)

2. 0.020 481,777.625 ↑ 1.0 1 1

Unique (cost=208,297.17..208,297.24 rows=1 width=233) (actual time=481,777.607..481,777.625 rows=1 loops=1)

3. 0.140 481,777.605 ↓ 14.0 14 1

Sort (cost=208,297.17..208,297.17 rows=1 width=233) (actual time=481,777.604..481,777.605 rows=14 loops=1)

  • Sort Key: p.protocolo_processo, p.id_processo, p.id_imovel, pv.nome_razao, (CASE WHEN (pv.tipo_pessoa = 'F'::bpchar) THEN regexp_replace((pv.cpf)::text, '(\d{3})(\d{3})(\d{3})'::text, '\1.\2.\3-'::text) ELSE regexp_replace((pv.cnpj)::text, '( (...)
  • Sort Method: quicksort Memory: 32kB
4. 0.935 481,777.465 ↓ 14.0 14 1

Nested Loop (cost=6.23..208,297.16 rows=1 width=233) (actual time=481,691.522..481,777.465 rows=14 loops=1)

  • Join Filter: (p.id_unidade_regional = ur.id_unidade_regional)
  • Rows Removed by Join Filter: 252
5. 0.069 481,761.956 ↓ 14.0 14 1

Nested Loop Left Join (cost=6.23..208,060.31 rows=1 width=228) (actual time=481,690.051..481,761.956 rows=14 loops=1)

  • Join Filter: (sitdevfiscal.id_situacao_devolucao_fiscal = p.flg_pendencia_devolucao_fiscal_id_situacao)
  • Rows Removed by Join Filter: 154
6. 0.064 481,761.845 ↓ 14.0 14 1

Nested Loop Semi Join (cost=6.23..208,059.06 rows=1 width=197) (actual time=481,690.036..481,761.845 rows=14 loops=1)

7. 37.125 481,758.561 ↓ 140.0 140 1

Merge Join (cost=5.94..208,047.99 rows=1 width=201) (actual time=481,688.660..481,758.561 rows=140 loops=1)

  • Merge Cond: (n.id_notificacao = m.id_notificacao)
8. 141,602.853 481,709.018 ↓ 18,428.6 405,430 1

Nested Loop (cost=5.66..228,714.56 rows=22 width=205) (actual time=40.705..481,709.018 rows=405,430 loops=1)

  • Join Filter: (v.id_processo = n.id_processo)
  • Rows Removed by Join Filter: 1610457424
9. 98.021 98.021 ↑ 1.4 6,573 1

Index Scan using id_notificacao_idx on notificacao n (cost=0.29..388.95 rows=9,215 width=8) (actual time=0.015..98.021 rows=6,573 loops=1)

10. 254,304.562 340,008.144 ↓ 11,670.1 245,073 6,573

Materialize (cost=5.38..225,422.94 rows=21 width=213) (actual time=0.006..51.728 rows=245,073 loops=6,573)

11. 84.888 85,703.582 ↓ 11,671.4 245,100 1

Nested Loop (cost=5.38..225,422.83 rows=21 width=213) (actual time=0.311..85,703.582 rows=245,100 loops=1)

12. 48.218 85,240.004 ↓ 8,415.3 25,246 1

Nested Loop (cost=5.09..225,421.13 rows=3 width=205) (actual time=0.305..85,240.004 rows=25,246 loops=1)

  • Join Filter: (iv.tipo_logradouro = tl.id_tipo_logradouro)
  • Rows Removed by Join Filter: 252460
13. 122.885 85,166.540 ↓ 8,415.3 25,246 1

Nested Loop (cost=5.09..225,419.49 rows=3 width=203) (actual time=0.300..85,166.540 rows=25,246 loops=1)

14. 33,846.416 84,866.765 ↓ 8,423.3 25,270 1

Nested Loop (cost=5.09..225,419.40 rows=3 width=185) (actual time=0.282..84,866.765 rows=25,270 loops=1)

  • Join Filter: (p.id_proprietario = pv.id_proprietario_versao)
  • Rows Removed by Join Filter: 256541040
15. 28,951.843 39,345.609 ↓ 8,423.3 25,270 1

Nested Loop (cost=5.09..224,561.12 rows=3 width=137) (actual time=0.091..39,345.609 rows=25,270 loops=1)

  • Join Filter: (p.id_imovel = iv.id_imovel_versao)
  • Rows Removed by Join Filter: 222931940
16. 393.888 664.816 ↓ 8,423.3 25,270 1

Nested Loop (cost=5.09..223,856.79 rows=3 width=73) (actual time=0.081..664.816 rows=25,270 loops=1)

17. 42.019 119.284 ↑ 1.0 25,274 1

Merge Join (cost=0.57..1,482.03 rows=25,274 width=53) (actual time=0.050..119.284 rows=25,274 loops=1)

  • Merge Cond: (p.id_processo = v.id_processo)
18. 37.744 37.744 ↑ 1.0 8,553 1

Index Scan using id_processo_idx on processo p (cost=0.29..289.09 rows=8,553 width=49) (actual time=0.009..37.744 rows=8,553 loops=1)

19. 39.521 39.521 ↑ 1.1 25,275 1

Index Only Scan using idx_vistorias_id_processo on vistorias v (cost=0.29..898.86 rows=26,551 width=4) (actual time=0.038..39.521 rows=25,275 loops=1)

  • Heap Fetches: 3799
20. 0.000 151.644 ↑ 1.0 1 25,274

Index Scan using id_historico_processo_idx on historico_processo hp (cost=4.52..8.79 rows=1 width=20) (actual time=0.004..0.006 rows=1 loops=25,274)

  • Index Cond: (id_historico_processo = (SubPlan 19))
  • Filter: (v.id_processo = processo_id_processo)
21.          

SubPlan (for Index Scan)

22. 75.822 303.288 ↑ 1.0 1 25,274

Aggregate (cost=4.22..4.23 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=25,274)

23. 227.466 227.466 ↑ 1.0 4 25,274

Index Scan using processo_id_processo_idx on historico_processo (cost=0.29..4.21 rows=4 width=4) (actual time=0.006..0.009 rows=4 loops=25,274)

  • Index Cond: (processo_id_processo = v.id_processo)
24. 75.822 303.288 ↑ 1.0 1 25,274

Aggregate (cost=4.22..4.23 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=25,274)

25. 227.466 227.466 ↑ 1.0 4 25,274

Index Scan using processo_id_processo_idx on historico_processo (cost=0.29..4.21 rows=4 width=4) (actual time=0.006..0.009 rows=4 loops=25,274)

  • Index Cond: (processo_id_processo = v.id_processo)
26. 9,726.256 9,728.950 ↑ 1.0 8,823 25,270

Materialize (cost=0.00..329.35 rows=8,823 width=68) (actual time=0.000..0.385 rows=8,823 loops=25,270)

27. 2.694 2.694 ↑ 1.0 8,823 1

Seq Scan on imovel_versao iv (cost=0.00..285.23 rows=8,823 width=68) (actual time=0.003..2.694 rows=8,823 loops=1)

28. 11,672.309 11,674.740 ↓ 1.0 10,153 25,270

Materialize (cost=0.00..427.16 rows=10,144 width=52) (actual time=0.000..0.462 rows=10,153 loops=25,270)

29. 2.431 2.431 ↓ 1.0 10,153 1

Seq Scan on proprietario_versao pv (cost=0.00..376.44 rows=10,144 width=52) (actual time=0.002..2.431 rows=10,153 loops=1)

30. 176.890 176.890 ↑ 1.0 1 25,270

Index Scan using pk_id_situacao_processo on situacao_processo sp (cost=0.00..0.02 rows=1 width=22) (actual time=0.006..0.007 rows=1 loops=25,270)

  • Index Cond: (id_situacao_processo = hp.situacao_id_situacao)
  • Filter: (id_situacao_processo > 0)
  • Rows Removed by Filter: 0
31. 25.242 25.246 ↑ 1.0 11 25,246

Materialize (cost=0.00..1.17 rows=11 width=10) (actual time=0.000..0.001 rows=11 loops=25,246)

32. 0.004 0.004 ↑ 1.0 11 1

Seq Scan on tipo_logradouro tl (cost=0.00..1.11 rows=11 width=10) (actual time=0.002..0.004 rows=11 loops=1)

33. 378.690 378.690 ↓ 1.4 10 25,246

Index Scan using idx_tramitacao_idp on tramitacao t (cost=0.29..0.50 rows=7 width=8) (actual time=0.006..0.015 rows=10 loops=25,246)

  • Index Cond: (id_processo = v.id_processo)
34. 12.418 12.418 ↑ 1.0 1 1

Index Scan using idx_multa_idn on multa m (cost=0.28..125.09 rows=1 width=4) (actual time=0.975..12.418 rows=1 loops=1)

  • Filter: (id_multa = 22289)
  • Rows Removed by Filter: 40748
35. 0.280 3.220 ↓ 0.0 0 140

Nested Loop (cost=0.29..5.67 rows=1 width=8) (actual time=0.023..0.023 rows=0 loops=140)

36. 0.700 0.700 ↑ 1.0 1 140

Index Scan using tramitacao_pkey on tramitacao t2 (cost=0.29..0.34 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=140)

  • Index Cond: (id_tramitacao = t.id_tramitacao)
37. 0.420 2.240 ↓ 0.0 0 140

Index Scan using pk_id_vistoria on vistorias v2 (cost=0.00..5.33 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=140)

  • Index Cond: (id_vistoria = t2.vistoria_id_vistoria)
  • Filter: (t2.id_tramitacao = (SubPlan 20))
  • Rows Removed by Filter: 1
38.          

SubPlan (for Index Scan)

39. 0.420 1.820 ↑ 1.0 1 140

Aggregate (cost=5.25..5.26 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=140)

40. 1.400 1.400 ↓ 3.0 18 140

Index Scan using idx_sdf_idsdf on tramitacao (cost=0.29..5.23 rows=6 width=4) (actual time=0.002..0.010 rows=18 loops=140)

  • Index Cond: (vistoria_id_vistoria = v2.id_vistoria)
41. 0.042 0.042 ↑ 1.0 11 14

Seq Scan on situacao_devolucao_fiscal sitdevfiscal (cost=0.00..1.11 rows=11 width=33) (actual time=0.003..0.003 rows=11 loops=14)

42. 0.028 0.028 ↑ 1.0 19 14

Seq Scan on unidade_regional ur (cost=0.00..1.19 rows=19 width=9) (actual time=0.001..0.002 rows=19 loops=14)

43.          

SubPlan (for Nested Loop)

44. 0.014 1.288 ↑ 1.0 1 14

Aggregate (cost=26.95..26.96 rows=1 width=4) (actual time=0.092..0.092 rows=1 loops=14)

45. 0.028 1.274 ↑ 1.0 2 14

Unique (cost=26.92..26.93 rows=2 width=16) (actual time=0.090..0.091 rows=2 loops=14)

46. 0.098 1.246 ↑ 1.0 2 14

Sort (cost=26.92..26.92 rows=2 width=16) (actual time=0.089..0.089 rows=2 loops=14)

  • Sort Key: (min(CASE WHEN (subn.prazo_recurso_concedido IS NOT NULL) THEN subn.prazo_recurso_concedido ELSE (COALESCE(subn.data_publicacao_dom, subn.data_entrega) + CASE WHEN (subn.prazo < 15) THEN 15 ELSE subn.prazo (...)
  • Sort Method: quicksort Memory: 25kB
47. 0.028 1.148 ↑ 1.0 2 14

Append (cost=13.35..26.91 rows=2 width=16) (actual time=0.038..0.082 rows=2 loops=14)

48. 0.014 0.518 ↑ 1.0 1 14

Aggregate (cost=13.35..13.36 rows=1 width=16) (actual time=0.037..0.037 rows=1 loops=14)

49. 0.014 0.504 ↓ 0.0 0 14

Result (cost=7.94..13.34 rows=1 width=16) (actual time=0.036..0.036 rows=0 loops=14)

  • One-Time Filter: (p.id_situacao <> ALL ('{7,8}'::integer[]))
50. 0.028 0.490 ↓ 0.0 0 14

Nested Loop (cost=7.94..13.34 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=14)

51. 0.224 0.462 ↓ 0.0 0 14

Index Scan using idx_notificacao_idp on notificacao subn (cost=7.65..10.81 rows=1 width=20) (actual time=0.033..0.033 rows=0 loops=14)

  • Index Cond: (id_processo = p.id_processo)
  • Filter: ((COALESCE(prazo_recurso_concedido, data_publicacao_dom, data_entrega) IS NOT NULL) AND (NOT (hashed SubPlan 2)) AND (id_situacao_notificacao = 1))
  • Rows Removed by Filter: 6
52.          

SubPlan (for Index Scan)

53. 0.000 0.238 ↓ 3.0 3 14

Nested Loop (cost=0.57..7.36 rows=1 width=4) (actual time=0.006..0.017 rows=3 loops=14)

54. 0.070 0.070 ↓ 3.0 6 14

Index Scan using idx_notificacao_idp on notificacao n11 (cost=0.29..3.44 rows=2 width=4) (actual time=0.002..0.005 rows=6 loops=14)

  • Index Cond: (id_processo = p.id_processo)
55. 0.168 0.168 ↓ 0.0 0 84

Index Only Scan using idx_multa_idn on multa m1a (cost=0.28..1.95 rows=1 width=4) (actual time=0.001..0.002 rows=0 loops=84)

  • Index Cond: (id_notificacao = n11.id_notificacao)
  • Heap Fetches: 0
56. 0.000 0.000 ↓ 0.0 0

Index Scan using vistoria_servico_pkey on vistoria_servico vs1 (cost=0.29..2.51 rows=1 width=4) (never executed)

  • Index Cond: (id_vistoria_servico = subn.id_vistoria_servico)
  • Filter: ((ativo = 1) AND (id_situacao_vistoria_servico = 3))
57. 0.028 0.602 ↑ 1.0 1 14

Aggregate (cost=13.52..13.53 rows=1 width=16) (actual time=0.043..0.043 rows=1 loops=14)

58. 0.028 0.574 ↓ 2.0 2 14

Result (cost=0.85..13.52 rows=1 width=16) (actual time=0.017..0.041 rows=2 loops=14)

  • One-Time Filter: (p.id_situacao <> ALL ('{7,8}'::integer[]))
59. 0.014 0.546 ↓ 2.0 2 14

Nested Loop (cost=0.85..13.52 rows=1 width=16) (actual time=0.016..0.039 rows=2 loops=14)

60. 0.070 0.308 ↓ 4.0 4 14

Nested Loop (cost=0.57..8.49 rows=1 width=8) (actual time=0.007..0.022 rows=4 loops=14)

61. 0.070 0.070 ↓ 3.0 6 14

Index Scan using idx_notificacao_idp on notificacao subn2 (cost=0.29..3.44 rows=2 width=12) (actual time=0.002..0.005 rows=6 loops=14)

  • Index Cond: (id_processo = p.id_processo)
62. 0.168 0.168 ↑ 1.0 1 84

Index Scan using vistoria_servico_pkey on vistoria_servico vs2 (cost=0.29..2.51 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=84)

  • Index Cond: (id_vistoria_servico = subn2.id_vistoria_servico)
  • Filter: ((id_situacao_vistoria_servico = 3) AND (ativo = 1))
  • Rows Removed by Filter: 0
63. 0.140 0.224 ↓ 0.0 0 56

Index Scan using idx_multa_idn on multa subm (cost=0.28..5.02 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=56)

  • Index Cond: (id_notificacao = subn2.id_notificacao)
  • Filter: ((COALESCE(prazo_recurso_concedido, data_publicacao_dom, data_entrega) IS NOT NULL) AND (id_situacao_multa = ANY ('{1,4}'::integer[])) AND ((SubPlan 1) = id_multa))
64.          

SubPlan (for Index Scan)

65. 0.028 0.084 ↑ 1.0 1 28

Aggregate (cost=2.50..2.51 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=28)

66. 0.056 0.056 ↑ 1.0 1 28

Index Scan using idx_multa_idn on multa (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=28)

  • Index Cond: (id_notificacao = subn2.id_notificacao)
  • Filter: (id_situacao_multa = ANY ('{1,4}'::integer[]))
67. 0.406 3.332 ↑ 1.0 1 14

Index Scan using idx_tramitacao_idp on tramitacao tfpag1 (cost=0.29..49.51 rows=1 width=16) (actual time=0.238..0.238 rows=1 loops=14)

  • Index Cond: (id_processo = p.id_processo)
  • Filter: (((protocolo)::text = (p.protocolo_processo)::text) AND (id_tramitacao = (SubPlan 4)))
  • Rows Removed by Filter: 19
68.          

SubPlan (for Index Scan)

69. 0.532 2.926 ↑ 1.0 1 266

Aggregate (cost=6.18..6.19 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=266)

70. 2.394 2.394 ↓ 19.0 19 266

Index Scan using idx_tramitacao_idp on tramitacao tsfpag1 (cost=0.29..6.17 rows=1 width=4) (actual time=0.002..0.009 rows=19 loops=266)

  • Index Cond: (id_processo = tfpag1.id_processo)
  • Filter: ((protocolo)::text = (tfpag1.protocolo)::text)
  • Rows Removed by Filter: 1
71. 0.322 3.332 ↑ 1.0 1 14

Index Scan using idx_tramitacao_idp on tramitacao tfpag2 (cost=0.29..56.60 rows=1 width=20) (actual time=0.237..0.238 rows=1 loops=14)

  • Index Cond: (id_processo = p.id_processo)
  • Filter: (((protocolo)::text = (p.protocolo_processo)::text) AND (id_tramitacao = (SubPlan 9)))
  • Rows Removed by Filter: 19
72.          

SubPlan (for Index Scan)

73. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2.36..2.37 rows=1 width=21) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using usuario_pkey on usuario u1 (cost=0.14..2.36 rows=1 width=21) (never executed)

  • Index Cond: (id_usuario = tfpag2.id_usuario_destino)
75. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2.36..2.37 rows=1 width=21) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Index Scan using usuario_pkey on usuario u1fpag (cost=0.14..2.36 rows=1 width=21) (never executed)

  • Index Cond: (id_usuario = tfpag2.id_usuario_origem)
77. 0.028 0.084 ↑ 1.0 1 14

HashAggregate (cost=2.36..2.37 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=14)

78. 0.056 0.056 ↑ 1.0 1 14

Index Scan using usuario_pkey on usuario u1fpag_1 (cost=0.14..2.36 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: (id_usuario = tfpag2.id_usuario_destino)
79. 0.532 2.926 ↑ 1.0 1 266

Aggregate (cost=6.18..6.19 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=266)

80. 2.394 2.394 ↓ 19.0 19 266

Index Scan using idx_tramitacao_idp on tramitacao tsfpag2 (cost=0.29..6.17 rows=1 width=4) (actual time=0.002..0.009 rows=19 loops=266)

  • Index Cond: (id_processo = tfpag2.id_processo)
  • Filter: ((protocolo)::text = (tfpag2.protocolo)::text)
  • Rows Removed by Filter: 1
81. 0.252 3.486 ↑ 1.0 1 14

Index Scan using idx_tramitacao_idp on tramitacao tfpag3 (cost=0.29..52.81 rows=1 width=16) (actual time=0.249..0.249 rows=1 loops=14)

  • Index Cond: (id_processo = p.id_processo)
  • Filter: (((protocolo)::text = (p.protocolo_processo)::text) AND (id_tramitacao = (SubPlan 15)))
  • Rows Removed by Filter: 19
82.          

SubPlan (for Index Scan)

83. 0.000 0.000 ↓ 0.0 0

Result (cost=2.20..2.21 rows=1 width=0) (never executed)

84.          

Initplan (for Result)

85. 0.000 0.000 ↓ 0.0 0

Seq Scan on centro_custo cfpag3 (cost=0.00..1.10 rows=1 width=18) (never executed)

  • Filter: (id_centro_custo = 7)
86. 0.000 0.000 ↓ 0.0 0

Seq Scan on centro_custo cfpag3_1 (cost=0.00..1.10 rows=1 width=18) (never executed)

  • Filter: (id_centro_custo = tfpag3.id_cc_destino)
87. 0.042 0.042 ↑ 1.0 1 14

Seq Scan on centro_custo cfpag3_2 (cost=0.00..1.10 rows=1 width=18) (actual time=0.002..0.003 rows=1 loops=14)

  • Filter: (id_centro_custo = tfpag3.id_cc_destino)
  • Rows Removed by Filter: 7
88. 0.532 3.192 ↑ 1.0 1 266

Aggregate (cost=6.18..6.19 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=266)

89. 2.660 2.660 ↓ 19.0 19 266

Index Scan using idx_tramitacao_idp on tramitacao tsfpag3 (cost=0.29..6.17 rows=1 width=4) (actual time=0.002..0.010 rows=19 loops=266)

  • Index Cond: (id_processo = tfpag3.id_processo)
  • Filter: ((protocolo)::text = (tfpag3.protocolo)::text)
  • Rows Removed by Filter: 1
90. 0.182 3.108 ↑ 1.0 1 14

Index Scan using idx_tramitacao_idp on tramitacao tfpag4 (cost=0.29..49.49 rows=1 width=4) (actual time=0.222..0.222 rows=1 loops=14)

  • Index Cond: (id_processo = p.id_processo)
  • Filter: (((protocolo)::text = (p.protocolo_processo)::text) AND (id_tramitacao = (SubPlan 17)))
  • Rows Removed by Filter: 19
91.          

SubPlan (for Index Scan)

92. 0.532 2.926 ↑ 1.0 1 266

Aggregate (cost=6.18..6.19 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=266)

93. 2.394 2.394 ↓ 19.0 19 266

Index Scan using idx_tramitacao_idp on tramitacao tsfpag4 (cost=0.29..6.17 rows=1 width=4) (actual time=0.002..0.009 rows=19 loops=266)

  • Index Cond: (id_processo = tfpag4.id_processo)
  • Filter: ((protocolo)::text = (tfpag4.protocolo)::text)
  • Rows Removed by Filter: 1
Total runtime : 481,786.800 ms