explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dvgw

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 412,907.723 ↑ 1.0 1 1

Limit (cost=208,485.43..208,485.50 rows=1 width=233) (actual time=412,907.704..412,907.723 rows=1 loops=1)

2. 0.019 412,907.721 ↑ 1.0 1 1

Unique (cost=208,485.43..208,485.50 rows=1 width=233) (actual time=412,907.703..412,907.721 rows=1 loops=1)

3. 0.152 412,907.702 ↓ 14.0 14 1

Sort (cost=208,485.43..208,485.43 rows=1 width=233) (actual time=412,907.702..412,907.702 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. 1.303 412,907.550 ↓ 14.0 14 1

Nested Loop (cost=323.77..208,485.42 rows=1 width=233) (actual time=412,834.185..412,907.550 rows=14 loops=1)

  • Join Filter: (p.id_unidade_regional = ur.id_unidade_regional)
  • Rows Removed by Join Filter: 252
5. 0.063 412,876.413 ↓ 14.0 14 1

Nested Loop Left Join (cost=323.77..208,248.57 rows=1 width=228) (actual time=412,817.001..412,876.413 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.149 412,876.322 ↓ 14.0 14 1

Nested Loop Semi Join (cost=323.77..208,247.32 rows=1 width=197) (actual time=412,816.985..412,876.322 rows=14 loops=1)

7. 58.594 412,872.253 ↓ 140.0 140 1

Merge Join (cost=323.48..208,236.26 rows=1 width=201) (actual time=412,815.286..412,872.253 rows=140 loops=1)

  • Merge Cond: (n.id_notificacao = m.id_notificacao)
8. 147,497.447 412,813.563 ↓ 18,424.7 405,343 1

Nested Loop (cost=320.97..229,024.95 rows=22 width=205) (actual time=1,552.057..412,813.563 rows=405,343 loops=1)

  • Join Filter: (v.id_processo = n.id_processo)
  • Rows Removed by Join Filter: 1610063298
9. 108.712 108.712 ↑ 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.050..108.712 rows=6,573 loops=1)

10. 262,894.154 265,207.404 ↓ 11,667.3 245,013 6,573

Materialize (cost=320.68..225,733.33 rows=21 width=213) (actual time=0.007..40.348 rows=245,013 loops=6,573)

11. 215.290 2,313.250 ↓ 11,669.0 245,050 1

Nested Loop (cost=320.68..225,733.23 rows=21 width=213) (actual time=7.965..2,313.250 rows=245,050 loops=1)

  • Join Filter: (iv.tipo_logradouro = tl.id_tipo_logradouro)
  • Rows Removed by Join Filter: 2450500
12. 242.494 1,852.910 ↓ 11,669.0 245,050 1

Nested Loop (cost=320.68..225,728.62 rows=21 width=211) (actual time=7.955..1,852.910 rows=245,050 loops=1)

13. 279.562 1,365.366 ↓ 11,669.0 245,050 1

Nested Loop (cost=320.40..225,721.19 rows=21 width=163) (actual time=7.934..1,365.366 rows=245,050 loops=1)

14. 52.344 840.754 ↓ 11,669.0 245,050 1

Nested Loop (cost=320.11..225,713.96 rows=21 width=99) (actual time=7.901..840.754 rows=245,050 loops=1)

15. 43.457 485.530 ↓ 8,413.3 25,240 1

Nested Loop (cost=319.82..225,712.26 rows=3 width=91) (actual time=7.869..485.530 rows=25,240 loops=1)

16. 223.188 391.545 ↓ 8,421.3 25,264 1

Nested Loop (cost=319.82..225,712.16 rows=3 width=73) (actual time=7.842..391.545 rows=25,264 loops=1)

17. 47.554 92.553 ↓ 1.0 25,268 1

Hash Join (cost=315.31..3,655.10 rows=25,238 width=53) (actual time=7.747..92.553 rows=25,268 loops=1)

  • Hash Cond: (v.id_processo = p.id_processo)
18. 37.284 37.284 ↑ 1.0 26,547 1

Seq Scan on vistorias v (cost=0.00..2,754.96 rows=26,596 width=4) (actual time=0.018..37.284 rows=26,547 loops=1)

19. 3.320 7.715 ↓ 1.0 8,551 1

Hash (cost=208.47..208.47 rows=8,547 width=49) (actual time=7.715..7.715 rows=8,551 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 759kB
20. 4.395 4.395 ↓ 1.0 8,551 1

Seq Scan on processo p (cost=0.00..208.47 rows=8,547 width=49) (actual time=0.019..4.395 rows=8,551 loops=1)

21. 0.000 75.804 ↑ 1.0 1 25,268

Index Scan using id_historico_processo_idx on historico_processo hp (cost=4.52..8.79 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=25,268)

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

SubPlan (for Index Scan)

23. 25.268 176.876 ↑ 1.0 1 25,268

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

24. 151.608 151.608 ↑ 1.0 4 25,268

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

  • Index Cond: (processo_id_processo = v.id_processo)
25. 25.268 176.876 ↑ 1.0 1 25,268

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

26. 151.608 151.608 ↑ 1.0 4 25,268

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

  • Index Cond: (processo_id_processo = v.id_processo)
27. 50.528 50.528 ↑ 1.0 1 25,264

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

  • Index Cond: (id_situacao_processo = hp.situacao_id_situacao)
  • Filter: (id_situacao_processo > 0)
  • Rows Removed by Filter: 0
28. 302.880 302.880 ↓ 1.4 10 25,240

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

  • Index Cond: (id_processo = v.id_processo)
29. 245.050 245.050 ↑ 1.0 1 245,050

Index Scan using imovel_versao_pkey on imovel_versao iv (cost=0.29..0.33 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=245,050)

  • Index Cond: (id_imovel_versao = p.id_imovel)
30. 245.050 245.050 ↑ 1.0 1 245,050

Index Scan using proprietario_versao_pkey on proprietario_versao pv (cost=0.29..0.34 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=245,050)

  • Index Cond: (id_proprietario_versao = p.id_proprietario)
31. 245.046 245.050 ↑ 1.0 11 245,050

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

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.003..0.004 rows=11 loops=1)

33. 0.055 0.096 ↑ 1.0 1 1

Sort (cost=2.51..2.51 rows=1 width=4) (actual time=0.075..0.096 rows=1 loops=1)

  • Sort Key: m.id_notificacao
  • Sort Method: quicksort Memory: 25kB
34. 0.041 0.041 ↑ 1.0 1 1

Index Scan using unicidade_multa_numero_ar on multa m (cost=0.28..2.50 rows=1 width=4) (actual time=0.040..0.041 rows=1 loops=1)

  • Index Cond: (id_multa = 22289)
35. 0.140 3.920 ↓ 0.0 0 140

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

36. 0.560 0.560 ↑ 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.004 rows=1 loops=140)

  • Index Cond: (id_tramitacao = t.id_tramitacao)
37. 1.820 3.220 ↓ 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.023..0.023 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.280 1.400 ↑ 1.0 1 140

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

40. 1.120 1.120 ↓ 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.008 rows=18 loops=140)

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

Seq Scan on situacao_devolucao_fiscal sitdevfiscal (cost=0.00..1.11 rows=11 width=33) (actual time=0.001..0.002 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 14.812 ↑ 1.0 1 14

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

45. 0.028 14.798 ↑ 1.0 2 14

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

46. 0.084 14.770 ↑ 1.0 2 14

Sort (cost=26.92..26.92 rows=2 width=16) (actual time=1.055..1.055 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 14.686 ↑ 1.0 2 14

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

48. 0.014 3.402 ↑ 1.0 1 14

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

49. 0.028 3.388 ↓ 0.0 0 14

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

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

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

51. 0.280 3.346 ↓ 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.239..0.239 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.042 3.066 ↓ 3.0 3 14

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

54. 0.084 0.084 ↓ 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.006 rows=6 loops=14)

  • Index Cond: (id_processo = p.id_processo)
55. 2.940 2.940 ↓ 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.035..0.035 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.042 11.256 ↑ 1.0 1 14

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

58. 0.028 11.214 ↓ 2.0 2 14

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

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

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

60. 0.014 0.336 ↓ 4.0 4 14

Nested Loop (cost=0.57..8.49 rows=1 width=8) (actual time=0.009..0.024 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.252 0.252 ↑ 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.003..0.003 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. 10.724 10.808 ↓ 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.192..0.193 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.002..0.002 rows=1 loops=28)

  • Index Cond: (id_notificacao = subn2.id_notificacao)
  • Filter: (id_situacao_multa = ANY ('{1,4}'::integer[]))
67. 2.338 5.264 ↑ 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.376..0.376 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.392 3.472 ↑ 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.248..0.248 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.042 0.154 ↑ 1.0 1 14

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

78. 0.112 0.112 ↑ 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.008..0.008 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.224 3.206 ↑ 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.229..0.229 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.056 0.056 ↑ 1.0 1 14

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

  • Filter: (id_centro_custo = tfpag3.id_cc_destino)
  • Rows Removed by Filter: 7
88. 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)

89. 2.394 2.394 ↓ 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.009 rows=19 loops=266)

  • Index Cond: (id_processo = tfpag3.id_processo)
  • Filter: ((protocolo)::text = (tfpag3.protocolo)::text)
  • Rows Removed by Filter: 1
90. 0.392 3.052 ↑ 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.218..0.218 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.266 2.660 ↑ 1.0 1 266

Aggregate (cost=6.18..6.19 rows=1 width=4) (actual time=0.010..0.010 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 : 412,934.330 ms