explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ukxJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,826.216 ↓ 0.0 0 1

Subquery Scan on tmp (cost=6,147.89..7,656.22 rows=2 width=1,255) (actual time=1,826.216..1,826.216 rows=0 loops=1)

2. 0.000 1,826.214 ↓ 0.0 0 1

Append (cost=6,147.89..7,656.19 rows=2 width=800) (actual time=1,826.214..1,826.214 rows=0 loops=1)

3. 0.001 0.214 ↓ 0.0 0 1

WindowAgg (cost=6,147.89..6,147.92 rows=1 width=325) (actual time=0.214..0.214 rows=0 loops=1)

4. 0.013 0.213 ↓ 0.0 0 1

Sort (cost=6,147.89..6,147.90 rows=1 width=325) (actual time=0.213..0.213 rows=0 loops=1)

  • Sort Key: solicitacao.co_protocolo
  • Sort Method: quicksort Memory: 25kB
5. 0.002 0.200 ↓ 0.0 0 1

Hash Left Join (cost=5,518.71..6,147.88 rows=1 width=325) (actual time=0.200..0.200 rows=0 loops=1)

  • Hash Cond: ((solicitacao.co_protocolo)::text = (solicitacaolida.co_protocolo)::text)
6. 0.014 0.198 ↓ 0.0 0 1

Hash Join (cost=5,422.41..6,051.56 rows=1 width=324) (actual time=0.198..0.198 rows=0 loops=1)

  • Hash Cond: (solicitacao.id_tipo_status = tipostatus.id_tipo_status)
7. 0.010 0.174 ↓ 0.0 0 1

Hash Join (cost=5,421.09..6,050.23 rows=1 width=108) (actual time=0.174..0.174 rows=0 loops=1)

  • Hash Cond: (u.id_pessoa = solicitacao.id_pessoa)
8. 0.005 0.005 ↑ 10,700.0 1 1

Seq Scan on tb_usuario u (cost=0.00..589.00 rows=10,700 width=8) (actual time=0.005..0.005 rows=1 loops=1)

9. 0.000 0.159 ↓ 0.0 0 1

Hash (cost=5,421.08..5,421.08 rows=1 width=104) (actual time=0.159..0.159 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
10. 0.010 0.159 ↓ 0.0 0 1

Hash Right Join (cost=5,228.33..5,421.08 rows=1 width=104) (actual time=0.159..0.159 rows=0 loops=1)

  • Hash Cond: (sub_compartilhamento.id_solicitacao = solicitacao.id_solicitacao)
  • Filter: (((solicitacao.id_pessoa = 16,031) AND (tiposolicitacao.id_tipo_solicitacao = 2)) OR (((solicitacao.co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])) AN (...)
11. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,117.28..5,198.42 rows=8,114 width=16) (never executed)

  • Group Key: sub_compartilhamento.id_solicitacao, subtipo_solicitacao_interna.id_tipo_solicitacao, solicitacao_interna.id_subtipo_solicitacao, solicitacao_interna.id_tipo_status
12. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=4,502.36..5,015.86 rows=8,114 width=16) (never executed)

  • Hash Cond: (solicitacao_interna.id_subtipo_solicitacao = subtipo_solicitacao_interna.id_subtipo_solicitacao)
13. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=4,489.93..4,891.86 rows=8,114 width=12) (never executed)

  • Hash Cond: (sub_compartilhamento.id_solicitacao = solicitacao_interna.id_solicitacao)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_compartilhamento sub_compartilhamento (cost=0.00..224.31 rows=8,114 width=4) (never executed)

  • Filter: (dt_exclusao IS NULL)
15. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,521.08..3,521.08 rows=77,508 width=12) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_solicitacao solicitacao_interna (cost=0.00..3,521.08 rows=77,508 width=12) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.97..9.97 rows=197 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_subtipo_solicitacao subtipo_solicitacao_interna (cost=0.00..9.97 rows=197 width=8) (never executed)

19. 0.000 0.149 ↓ 0.0 0 1

Hash (cost=110.80..110.80 rows=20 width=100) (actual time=0.149..0.149 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
20. 0.011 0.149 ↓ 0.0 0 1

Hash Join (cost=31.42..110.80 rows=20 width=100) (actual time=0.149..0.149 rows=0 loops=1)

  • Hash Cond: (subtiposolicitacao.id_tipo_solicitacao = tiposolicitacao.id_tipo_solicitacao)
21. 0.013 0.133 ↓ 0.0 0 1

Hash Join (cost=30.15..109.25 rows=20 width=94) (actual time=0.133..0.133 rows=0 loops=1)

  • Hash Cond: (solicitacao.id_subtipo_solicitacao = subtiposolicitacao.id_subtipo_solicitacao)
22. 0.001 0.030 ↓ 0.0 0 1

Bitmap Heap Scan on tb_solicitacao solicitacao (cost=17.71..96.55 rows=20 width=54) (actual time=0.030..0.030 rows=0 loops=1)

  • Recheck Cond: ((id_pessoa = 16,031) OR ((co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])))
  • Filter: ((id_solicitacao_pai IS NULL) AND (id_unidade_administrativa IS NULL))
23. 0.000 0.029 ↓ 0.0 0 1

BitmapOr (cost=17.71..17.71 rows=21 width=0) (actual time=0.029..0.029 rows=0 loops=1)

24. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on ind_solicitacao_id_pessoa (cost=0.00..4.43 rows=18 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (id_pessoa = 16,031)
25. 0.022 0.022 ↓ 0.0 0 1

Bitmap Index Scan on ind_tb_solicitacao_co_protocolo (cost=0.00..13.28 rows=3 width=0) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[]))
26. 0.047 0.090 ↑ 1.0 197 1

Hash (cost=9.97..9.97 rows=197 width=44) (actual time=0.090..0.090 rows=197 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
27. 0.043 0.043 ↑ 1.0 197 1

Seq Scan on tb_subtipo_solicitacao subtiposolicitacao (cost=0.00..9.97 rows=197 width=44) (actual time=0.003..0.043 rows=197 loops=1)

28. 0.003 0.005 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=10) (actual time=0.005..0.005 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
29. 0.002 0.002 ↑ 1.0 12 1

Seq Scan on tb_tipo_solicitacao tiposolicitacao (cost=0.00..1.12 rows=12 width=10) (actual time=0.002..0.002 rows=12 loops=1)

30. 0.005 0.010 ↓ 1.1 15 1

Hash (cost=1.14..1.14 rows=14 width=220) (actual time=0.010..0.010 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
31. 0.005 0.005 ↓ 1.1 15 1

Seq Scan on tb_tipo_status tipostatus (cost=0.00..1.14 rows=14 width=220) (actual time=0.004..0.005 rows=15 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Hash (cost=96.19..96.19 rows=9 width=23) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tb_solicitacao_lida solicitacaolida (cost=4.61..96.19 rows=9 width=23) (never executed)

  • Recheck Cond: (id_pessoa = 16,031)
  • Filter: (NOT in_administrador)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ind_solicitacao_lida_id_pessoa (cost=0.00..4.61 rows=25 width=0) (never executed)

  • Index Cond: (id_pessoa = 16,031)
35. 0.001 1,826.000 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1,288.12..1,508.27 rows=1 width=1,276) (actual time=1,826.000..1,826.000 rows=0 loops=1)

36. 0.000 1,825.999 ↓ 0.0 0 1

Hash Left Join (cost=1,288.12..1,508.26 rows=1 width=1,276) (actual time=1,825.999..1,825.999 rows=0 loops=1)

  • Hash Cond: ((t1.co_protocolo)::text = (solicitacaolida_1.co_protocolo)::text)
37. 0.000 1,825.999 ↓ 0.0 0 1

Hash Left Join (cost=1,191.83..1,411.92 rows=1 width=1,246) (actual time=1,825.999..1,825.999 rows=0 loops=1)

  • Hash Cond: (upper((t1.no_tipo_status)::text) = (ts.no_tipo_status)::text)
38. 26.088 1,825.999 ↓ 0.0 0 1

Hash Right Join (cost=1,190.51..1,410.59 rows=1 width=1,144) (actual time=1,825.999..1,825.999 rows=0 loops=1)

  • Hash Cond: (((sub_compartilhamento_1.co_protocolo)::text) = (t1.co_protocolo)::text)
  • Filter: (((u_1.id_pessoa = 16,031) AND (t1.id_tipo_solicitacao = 2)) OR (((t1.co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])) AND (t1_1.id_tipo_solicitacao = 2)))
  • Rows Removed by Filter: 60,834
39. 3.481 902.205 ↑ 1.5 5,261 1

HashAggregate (cost=531.06..612.20 rows=8,114 width=32) (actual time=901.198..902.205 rows=5,261 loops=1)

  • Group Key: sub_compartilhamento_1.co_protocolo, t1_1.id_tipo_solicitacao, t1_1.id_subtipo_solicitacao, tipo_status_interna.id_tipo_status
40. 13.505 898.724 ↓ 1.0 8,361 1

Hash Right Join (cost=327.05..429.63 rows=8,114 width=32) (actual time=823.131..898.724 rows=8,361 loops=1)

  • Hash Cond: ((t1_1.co_protocolo)::text = (sub_compartilhamento_1.co_protocolo)::text)
41. 57.977 882.297 ↓ 61.4 61,350 1

Hash Left Join (cost=1.32..18.27 rows=1,000 width=130) (actual time=820.093..882.297 rows=61,350 loops=1)

  • Hash Cond: (lower((t1_1.no_tipo_status)::text) = lower((tipo_status_interna.no_tipo_status)::text))
42. 824.290 824.290 ↓ 61.4 61,350 1

Function Scan on dblink t1_1 (cost=0.00..10.00 rows=1,000 width=244) (actual time=820.048..824.290 rows=61,350 loops=1)

43. 0.020 0.030 ↓ 1.1 15 1

Hash (cost=1.14..1.14 rows=14 width=122) (actual time=0.030..0.030 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
44. 0.010 0.010 ↓ 1.1 15 1

Seq Scan on tb_tipo_status tipo_status_interna (cost=0.00..1.14 rows=14 width=122) (actual time=0.010..0.010 rows=15 loops=1)

45. 1.405 2.922 ↓ 1.0 8,361 1

Hash (cost=224.31..224.31 rows=8,114 width=20) (actual time=2.922..2.922 rows=8,361 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 431kB
46. 1.517 1.517 ↓ 1.0 8,361 1

Seq Scan on tb_compartilhamento sub_compartilhamento_1 (cost=0.00..224.31 rows=8,114 width=20) (actual time=0.003..1.517 rows=8,361 loops=1)

  • Filter: (dt_exclusao IS NULL)
  • Rows Removed by Filter: 229
47. 18.228 897.706 ↓ 3,041.7 60,834 1

Hash (cost=659.20..659.20 rows=20 width=1,112) (actual time=897.706..897.706 rows=60,834 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 7,139kB
48. 36.380 879.478 ↓ 3,041.7 60,834 1

Hash Join (cost=16.50..659.20 rows=20 width=1,112) (actual time=841.298..879.478 rows=60,834 loops=1)

  • Hash Cond: ((u_1.no_login)::text = (t1.reporter_id_pessoa)::text)
49. 1.830 1.830 ↑ 1.0 10,700 1

Seq Scan on tb_usuario u_1 (cost=0.00..589.00 rows=10,700 width=22) (actual time=0.002..1.830 rows=10,700 loops=1)

50. 19.596 841.268 ↓ 3,067.5 61,350 1

Hash (cost=16.25..16.25 rows=20 width=1,620) (actual time=841.268..841.268 rows=61,350 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 7,591kB
51. 821.672 821.672 ↓ 3,067.5 61,350 1

Function Scan on dblink t1 (cost=0.00..16.25 rows=20 width=1,620) (actual time=809.291..821.672 rows=61,350 loops=1)

  • Filter: ((id_tipo_solicitacao = 2) OR ((co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])))
52. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.14..1.14 rows=14 width=220) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_tipo_status ts (cost=0.00..1.14 rows=14 width=220) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Hash (cost=96.19..96.19 rows=9 width=30) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tb_solicitacao_lida solicitacaolida_1 (cost=4.61..96.19 rows=9 width=30) (never executed)

  • Recheck Cond: (id_pessoa = 16,031)
  • Filter: (NOT in_administrador)
56. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ind_solicitacao_lida_id_pessoa (cost=0.00..4.61 rows=25 width=0) (never executed)

  • Index Cond: (id_pessoa = 16,031)
Planning time : 2.603 ms
Execution time : 1,830.869 ms