explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JuXD

Settings
# exclusive inclusive rows x rows loops node
1. 25.942 1,160.286 ↓ 336.6 23,561 1

Subquery Scan on vw_solicitacao_gestor_1 (cost=23,811.46..24,125.47 rows=70 width=1,046) (actual time=1,085.787..1,160.286 rows=23,561 loops=1)

  • Filter: (vw_solicitacao_gestor_1.id_tipo_solicitacao = 2)
  • Rows Removed by Filter: 30035
2. 161.302 1,134.344 ↓ 3.8 53,596 1

HashAggregate (cost=23,811.46..23,951.02 rows=13,956 width=1,027) (actual time=1,085.779..1,134.344 rows=53,596 loops=1)

  • Group Key: row_number() OVER (?), p.dc_login_ad, p.no_pessoa, c.no_cargo, COALESCE(ua.ds_posto_tratado, ua.co_unidade_administrativa), p_1.dc_login_ad, p_1.id_pessoa, tiposolicitacao.id_tipo_solicitacao, tiposolicitacao.no_tipo_solicitacao, solicit (...)
3. 78.702 973.042 ↓ 3.8 53,596 1

WindowAgg (cost=22,764.68..23,043.88 rows=13,956 width=1,027) (actual time=805.165..973.042 rows=53,596 loops=1)

4. 61.027 894.340 ↓ 3.8 53,596 1

Merge Left Join (cost=22,764.68..22,834.54 rows=13,956 width=1,027) (actual time=805.153..894.340 rows=53,596 loops=1)

  • Merge Cond: (solicitacao.id_solicitacao = subsubtarefa.id_solicitacao)
5. 91.225 743.659 ↓ 3.8 53,596 1

Sort (cost=16,621.45..16,656.34 rows=13,956 width=1,019) (actual time=715.496..743.659 rows=53,596 loops=1)

  • Sort Key: solicitacao.id_solicitacao
  • Sort Method: quicksort Memory: 34900kB
6. 126.624 652.434 ↓ 3.8 53,596 1

Hash Right Join (cost=13,570.03..15,660.67 rows=13,956 width=1,019) (actual time=506.312..652.434 rows=53,596 loops=1)

  • Hash Cond: ((solicitacaolida.id_solicitacao = solicitacao.id_solicitacao) AND (solicitacaolida.id_pessoa = solicitacao.id_pessoa))
7. 20.364 20.364 ↑ 1.0 41,416 1

Seq Scan on tb_solicitacao_lida solicitacaolida (cost=0.00..1,156.88 rows=41,498 width=8) (actual time=0.009..20.364 rows=41,416 loops=1)

  • Filter: in_administrador
  • Rows Removed by Filter: 18477
8. 46.536 505.446 ↓ 3.8 53,596 1

Hash (cost=13,360.69..13,360.69 rows=13,956 width=1,019) (actual time=505.446..505.446 rows=53,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 23364kB
9. 49.116 458.910 ↓ 3.8 53,596 1

Hash Left Join (cost=8,667.24..13,360.69 rows=13,956 width=1,019) (actual time=234.794..458.910 rows=53,596 loops=1)

  • Hash Cond: (solicitacao.id_pessoa_designado = p_1.id_pessoa)
10. 61.197 315.458 ↓ 3.8 52,666 1

Hash Join (cost=4,481.46..8,946.11 rows=13,956 width=1,005) (actual time=140.440..315.458 rows=52,666 loops=1)

  • Hash Cond: (solicitacao.id_pessoa = p.id_pessoa)
11. 41.862 114.229 ↓ 1.2 52,146 1

Hash Join (cost=14.05..3,754.94 rows=42,488 width=919) (actual time=0.398..114.229 rows=52,146 loops=1)

  • Hash Cond: (solicitacao.id_tipo_status = tipostatus.id_tipo_status)
12. 41.652 72.351 ↓ 1.1 52,146 1

Hash Join (cost=12.81..3,155.00 rows=46,350 width=703) (actual time=0.379..72.351 rows=52,146 loops=1)

  • Hash Cond: (solicitacao.id_subtipo_solicitacao = subtiposolicitacao.id_subtipo_solicitacao)
13. 30.329 30.329 ↓ 1.0 52,146 1

Seq Scan on tb_solicitacao solicitacao (cost=0.00..2,484.54 rows=52,144 width=244) (actual time=0.003..30.329 rows=52,146 loops=1)

  • Filter: (id_solicitacao_pai IS NULL)
  • Rows Removed by Filter: 8
14. 0.100 0.370 ↓ 1.1 167 1

Hash (cost=10.96..10.96 rows=148 width=463) (actual time=0.370..0.370 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.163 0.270 ↓ 1.1 167 1

Hash Join (cost=1.18..10.96 rows=148 width=463) (actual time=0.018..0.270 rows=167 loops=1)

  • Hash Cond: (subtiposolicitacao.id_tipo_solicitacao = tiposolicitacao.id_tipo_solicitacao)
16. 0.096 0.096 ↑ 1.0 167 1

Seq Scan on tb_subtipo_solicitacao subtiposolicitacao (cost=0.00..7.67 rows=167 width=45) (actual time=0.003..0.096 rows=167 loops=1)

17. 0.007 0.011 ↓ 1.1 9 1

Hash (cost=1.08..1.08 rows=8 width=422) (actual time=0.011..0.011 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
18. 0.004 0.004 ↓ 1.1 9 1

Seq Scan on tb_tipo_solicitacao tiposolicitacao (cost=0.00..1.08 rows=8 width=422) (actual time=0.002..0.004 rows=9 loops=1)

19. 0.006 0.016 ↓ 1.1 12 1

Hash (cost=1.11..1.11 rows=11 width=220) (actual time=0.016..0.016 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
20. 0.010 0.010 ↓ 1.1 12 1

Seq Scan on tb_tipo_status tipostatus (cost=0.00..1.11 rows=11 width=220) (actual time=0.004..0.010 rows=12 loops=1)

21. 6.727 140.032 ↓ 1.0 9,083 1

Hash (cost=4,354.59..4,354.59 rows=9,025 width=94) (actual time=140.032..140.032 rows=9,083 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 970kB
22. 8.541 133.305 ↓ 1.0 9,083 1

Hash Left Join (cost=2,810.98..4,354.59 rows=9,025 width=94) (actual time=69.755..133.305 rows=9,083 loops=1)

  • Hash Cond: (f.id_cargo = c.id_cargo)
23. 15.353 124.624 ↓ 1.0 9,083 1

Hash Right Join (cost=2,804.22..4,223.74 rows=9,025 width=76) (actual time=69.607..124.624 rows=9,083 loops=1)

  • Hash Cond: (f.id_pessoa = p.id_pessoa)
24. 11.438 51.891 ↑ 1.0 11,011 1

Hash Right Join (cost=508.80..1,836.98 rows=11,025 width=29) (actual time=12.220..51.891 rows=11,011 loops=1)

  • Hash Cond: (tl.id_funcionario = f.id_funcionario)
25. 5.982 29.845 ↓ 1.1 5,794 1

Hash Left Join (cost=76.87..1,316.34 rows=5,374 width=25) (actual time=1.591..29.845 rows=5,794 loops=1)

  • Hash Cond: (tl.id_unidade_administrativa = ua.id_unidade_administrativa)
26. 22.393 22.393 ↓ 1.1 5,794 1

Seq Scan on tb_lotacao tl (cost=0.00..1,165.58 rows=5,374 width=8) (actual time=0.107..22.393 rows=5,794 loops=1)

  • Filter: ((dt_assuncao IS NOT NULL) AND ((dt_remocao IS NULL) OR (dt_remocao > ('now'::cstring)::date)))
  • Rows Removed by Filter: 40985
27. 0.660 1.470 ↓ 1.0 1,472 1

Hash (cost=58.61..58.61 rows=1,461 width=25) (actual time=1.470..1.470 rows=1,472 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
28. 0.810 0.810 ↓ 1.0 1,472 1

Seq Scan on tb_unidade_administrativa ua (cost=0.00..58.61 rows=1,461 width=25) (actual time=0.005..0.810 rows=1,472 loops=1)

29. 5.101 10.608 ↑ 1.0 10,977 1

Hash (cost=294.11..294.11 rows=11,025 width=12) (actual time=10.608..10.608 rows=10,977 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 472kB
30. 5.507 5.507 ↑ 1.0 10,977 1

Seq Scan on tb_funcionario f (cost=0.00..294.11 rows=11,025 width=12) (actual time=0.010..5.507 rows=10,977 loops=1)

  • Filter: (dt_desligamento IS NULL)
  • Rows Removed by Filter: 3169
31. 5.968 57.380 ↑ 1.0 9,024 1

Hash (cost=2,182.61..2,182.61 rows=9,025 width=51) (actual time=57.380..57.380 rows=9,024 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 728kB
32. 15.717 51.412 ↑ 1.0 9,024 1

Hash Join (cost=1,711.47..2,182.61 rows=9,025 width=51) (actual time=30.254..51.412 rows=9,024 loops=1)

  • Hash Cond: (u.id_pessoa = p.id_pessoa)
33. 5.468 5.468 ↑ 1.0 10,091 1

Index Only Scan using ind_usuario_id_pessoa_id_usuario on tb_usuario u (cost=0.29..267.65 rows=10,091 width=4) (actual time=0.014..5.468 rows=10,091 loops=1)

  • Heap Fetches: 1162
34. 15.196 30.227 ↓ 1.0 27,478 1

Hash (cost=1,367.75..1,367.75 rows=27,475 width=47) (actual time=30.227..30.227 rows=27,478 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1967kB
35. 15.031 15.031 ↓ 1.0 27,478 1

Seq Scan on tb_pessoa p (cost=0.00..1,367.75 rows=27,475 width=47) (actual time=0.004..15.031 rows=27,478 loops=1)

36. 0.069 0.140 ↑ 1.0 167 1

Hash (cost=4.67..4.67 rows=167 width=26) (actual time=0.140..0.140 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 0.071 0.071 ↑ 1.0 167 1

Seq Scan on tb_cargo c (cost=0.00..4.67 rows=167 width=26) (actual time=0.003..0.071 rows=167 loops=1)

38. 4.597 94.336 ↓ 1.0 9,083 1

Hash (cost=4,072.97..4,072.97 rows=9,025 width=18) (actual time=94.336..94.336 rows=9,083 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 462kB
39. 11.159 89.739 ↓ 1.0 9,083 1

Hash Right Join (cost=2,727.35..4,072.97 rows=9,025 width=18) (actual time=53.906..89.739 rows=9,083 loops=1)

  • Hash Cond: (f_1.id_pessoa = p_1.id_pessoa)
40. 9.250 34.023 ↑ 1.0 11,011 1

Hash Right Join (cost=431.92..1,686.22 rows=11,025 width=4) (actual time=9.340..34.023 rows=11,011 loops=1)

  • Hash Cond: (tl_1.id_funcionario = f_1.id_funcionario)
41. 15.551 15.551 ↓ 1.1 5,794 1

Seq Scan on tb_lotacao tl_1 (cost=0.00..1,165.58 rows=5,374 width=8) (actual time=0.101..15.551 rows=5,794 loops=1)

  • Filter: ((dt_assuncao IS NOT NULL) AND ((dt_remocao IS NULL) OR (dt_remocao > ('now'::cstring)::date)))
  • Rows Removed by Filter: 40985
42. 4.384 9.222 ↑ 1.0 10,977 1

Hash (cost=294.11..294.11 rows=11,025 width=12) (actual time=9.222..9.222 rows=10,977 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 472kB
43. 4.838 4.838 ↑ 1.0 10,977 1

Seq Scan on tb_funcionario f_1 (cost=0.00..294.11 rows=11,025 width=12) (actual time=0.006..4.838 rows=10,977 loops=1)

  • Filter: (dt_desligamento IS NULL)
  • Rows Removed by Filter: 3169
44. 4.373 44.557 ↑ 1.0 9,024 1

Hash (cost=2,182.61..2,182.61 rows=9,025 width=18) (actual time=44.557..44.557 rows=9,024 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 459kB
45. 11.230 40.184 ↑ 1.0 9,024 1

Hash Join (cost=1,711.47..2,182.61 rows=9,025 width=18) (actual time=24.747..40.184 rows=9,024 loops=1)

  • Hash Cond: (u_1.id_pessoa = p_1.id_pessoa)
46. 4.246 4.246 ↑ 1.0 10,091 1

Index Only Scan using ind_usuario_id_pessoa_id_usuario on tb_usuario u_1 (cost=0.29..267.65 rows=10,091 width=4) (actual time=0.023..4.246 rows=10,091 loops=1)

  • Heap Fetches: 1162
47. 11.939 24.708 ↓ 1.0 27,478 1

Hash (cost=1,367.75..1,367.75 rows=27,475 width=18) (actual time=24.708..24.708 rows=27,478 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1156kB
48. 12.769 12.769 ↓ 1.0 27,478 1

Seq Scan on tb_pessoa p_1 (cost=0.00..1,367.75 rows=27,475 width=18) (actual time=0.007..12.769 rows=27,478 loops=1)

49. 0.022 89.654 ↑ 1.7 6 1

Sort (cost=6,143.24..6,143.26 rows=10 width=12) (actual time=89.649..89.654 rows=6 loops=1)

  • Sort Key: subsubtarefa.id_solicitacao
  • Sort Method: quicksort Memory: 25kB
50. 0.007 89.632 ↑ 1.7 6 1

Subquery Scan on subsubtarefa (cost=6,142.87..6,143.07 rows=10 width=12) (actual time=89.625..89.632 rows=6 loops=1)

51. 0.011 89.625 ↑ 1.7 6 1

HashAggregate (cost=6,142.87..6,142.97 rows=10 width=8) (actual time=89.623..89.625 rows=6 loops=1)

  • Group Key: solicitacao_1.id_solicitacao, count(subtarefa.id_solicitacao)
52. 0.028 89.614 ↑ 1.2 8 1

HashAggregate (cost=6,142.72..6,142.82 rows=10 width=8) (actual time=89.609..89.614 rows=8 loops=1)

  • Group Key: solicitacao_1.id_solicitacao, subtarefa.id_solicitacao
53. 16.816 89.586 ↑ 1.2 8 1

Hash Join (cost=3,136.47..6,142.65 rows=10 width=8) (actual time=73.514..89.586 rows=8 loops=1)

  • Hash Cond: (subtarefa.id_solicitacao_pai = solicitacao_1.id_solicitacao)
54. 25.900 25.900 ↑ 1.0 52,154 1

Seq Scan on tb_solicitacao subtarefa (cost=0.00..2,484.54 rows=52,154 width=8) (actual time=0.006..25.900 rows=52,154 loops=1)

55. 23.108 46.870 ↑ 1.0 52,154 1

Hash (cost=2,484.54..2,484.54 rows=52,154 width=4) (actual time=46.870..46.870 rows=52,154 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1834kB
56. 23.762 23.762 ↑ 1.0 52,154 1

Seq Scan on tb_solicitacao solicitacao_1 (cost=0.00..2,484.54 rows=52,154 width=4) (actual time=0.001..23.762 rows=52,154 loops=1)