explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D4CQ

Settings
# exclusive inclusive rows x rows loops node
1. 26.197 1,110.032 ↓ 336.6 23,561 1

Subquery Scan on vw_solicitacao_gestor_1 (cost=23,815.63..24,129.82 rows=70 width=1,046) (actual time=1,036.228..1,110.032 rows=23,561 loops=1)

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

HashAggregate (cost=23,815.63..23,955.27 rows=13,964 width=1,027) (actual time=1,036.218..1,083.835 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. 79.624 923.767 ↓ 3.8 53,596 1

WindowAgg (cost=22,768.25..23,047.61 rows=13,964 width=1,027) (actual time=755.395..923.767 rows=53,596 loops=1)

4. 61.085 844.143 ↓ 3.8 53,596 1

Merge Left Join (cost=22,768.25..22,838.15 rows=13,964 width=1,027) (actual time=755.367..844.143 rows=53,596 loops=1)

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

Sort (cost=16,623.74..16,658.65 rows=13,964 width=1,019) (actual time=663.599..691.293 rows=53,596 loops=1)

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

Hash Right Join (cost=13,571.71..15,662.36 rows=13,964 width=1,019) (actual time=471.853..600.713 rows=53,596 loops=1)

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

Seq Scan on tb_solicitacao_lida solicitacaolida (cost=0.00..1,156.88 rows=41,498 width=8) (actual time=0.008..19.589 rows=41,413 loops=1)

  • Filter: in_administrador
  • Rows Removed by Filter: 18476
8. 47.537 470.938 ↓ 3.8 53,596 1

Hash (cost=13,362.25..13,362.25 rows=13,964 width=1,019) (actual time=470.938..470.938 rows=53,596 loops=1)

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

Hash Left Join (cost=8,667.24..13,362.25 rows=13,964 width=1,019) (actual time=201.977..423.401 rows=53,596 loops=1)

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

Hash Join (cost=4,481.46..8,947.54 rows=13,964 width=1,005) (actual time=108.984..281.492 rows=52,666 loops=1)

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

Hash Join (cost=14.05..3,755.96 rows=42,512 width=919) (actual time=0.314..114.898 rows=52,146 loops=1)

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

Hash Join (cost=12.81..3,155.68 rows=46,377 width=703) (actual time=0.294..72.068 rows=52,146 loops=1)

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

Seq Scan on tb_solicitacao solicitacao (cost=0.00..2,484.84 rows=52,174 width=244) (actual time=0.003..30.199 rows=52,146 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.118 0.213 ↓ 1.1 167 1

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

  • Hash Cond: (subtiposolicitacao.id_tipo_solicitacao = tiposolicitacao.id_tipo_solicitacao)
16. 0.085 0.085 ↑ 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.085 rows=167 loops=1)

17. 0.005 0.010 ↓ 1.1 9 1

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

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

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

19. 0.007 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.009 0.009 ↓ 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.009 rows=12 loops=1)

21. 5.418 108.661 ↓ 1.0 9,083 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (tl.id_unidade_administrativa = ua.id_unidade_administrativa)
26. 15.454 15.454 ↓ 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.106..15.454 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.617 1.314 ↓ 1.0 1,472 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
28. 0.697 0.697 ↓ 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.004..0.697 rows=1,472 loops=1)

29. 4.518 9.378 ↑ 1.0 10,977 1

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

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

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

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

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

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

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

  • Hash Cond: (u.id_pessoa = p.id_pessoa)
33. 4.182 4.182 ↑ 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.010..4.182 rows=10,091 loops=1)

  • Heap Fetches: 1026
34. 11.938 23.988 ↓ 1.0 27,478 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1967kB
35. 12.050 12.050 ↓ 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.007..12.050 rows=27,478 loops=1)

36. 0.079 0.148 ↑ 1.0 167 1

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

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

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

38. 4.445 92.974 ↓ 1.0 9,083 1

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

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

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

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

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

  • Hash Cond: (tl_1.id_funcionario = f_1.id_funcionario)
41. 16.026 16.026 ↓ 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.102..16.026 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.405 9.165 ↑ 1.0 10,977 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 472kB
43. 4.760 4.760 ↑ 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.008..4.760 rows=10,977 loops=1)

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

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

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

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

  • Hash Cond: (u_1.id_pessoa = p_1.id_pessoa)
46. 4.258 4.258 ↑ 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.018..4.258 rows=10,091 loops=1)

  • Heap Fetches: 1026
47. 11.781 24.491 ↓ 1.0 27,478 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1156kB
48. 12.710 12.710 ↓ 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.006..12.710 rows=27,478 loops=1)

49. 0.020 91.765 ↑ 1.7 6 1

Sort (cost=6,144.51..6,144.54 rows=10 width=12) (actual time=91.760..91.765 rows=6 loops=1)

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

Subquery Scan on subsubtarefa (cost=6,144.15..6,144.35 rows=10 width=12) (actual time=91.738..91.745 rows=6 loops=1)

51. 0.017 91.739 ↑ 1.7 6 1

HashAggregate (cost=6,144.15..6,144.25 rows=10 width=8) (actual time=91.737..91.739 rows=6 loops=1)

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

HashAggregate (cost=6,143.99..6,144.10 rows=10 width=8) (actual time=91.717..91.722 rows=8 loops=1)

  • Group Key: solicitacao_1.id_solicitacao, subtarefa.id_solicitacao
53. 17.295 91.700 ↑ 1.2 8 1

Hash Join (cost=3,137.14..6,143.92 rows=10 width=8) (actual time=75.584..91.700 rows=8 loops=1)

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

Seq Scan on tb_solicitacao subtarefa (cost=0.00..2,484.84 rows=52,184 width=8) (actual time=0.007..26.656 rows=52,154 loops=1)

55. 23.640 47.749 ↑ 1.0 52,154 1

Hash (cost=2,484.84..2,484.84 rows=52,184 width=4) (actual time=47.749..47.749 rows=52,154 loops=1)

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

Seq Scan on tb_solicitacao solicitacao_1 (cost=0.00..2,484.84 rows=52,184 width=4) (actual time=0.002..24.109 rows=52,154 loops=1)

Planning time : 2.684 ms
Execution time : 1,122.975 ms