explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hBy

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 2,060.875 ↓ 1.5 3 1

Subquery Scan on tmp (cost=5,505.79..6,546.13 rows=2 width=1,255) (actual time=39.072..2,060.875 rows=3 loops=1)

2. 0.002 2,060.863 ↓ 1.5 3 1

Append (cost=5,505.79..6,546.11 rows=2 width=800) (actual time=39.063..2,060.863 rows=3 loops=1)

3. 0.016 39.114 ↓ 3.0 3 1

WindowAgg (cost=5,505.79..5,522.50 rows=1 width=325) (actual time=39.063..39.114 rows=3 loops=1)

4. 0.004 39.098 ↓ 3.0 3 1

Nested Loop (cost=5,505.79..5,522.48 rows=1 width=325) (actual time=39.050..39.098 rows=3 loops=1)

5. 0.003 39.067 ↓ 3.0 3 1

Nested Loop (cost=5,505.50..5,515.17 rows=1 width=321) (actual time=39.029..39.067 rows=3 loops=1)

6. 0.004 39.058 ↓ 3.0 3 1

Nested Loop (cost=5,505.37..5,514.40 rows=1 width=105) (actual time=39.022..39.058 rows=3 loops=1)

  • Join Filter: (tiposolicitacao.id_tipo_solicitacao = subtiposolicitacao.id_tipo_solicitacao)
  • Rows Removed by Join Filter: 33
7. 0.018 39.018 ↓ 36.0 36 1

Nested Loop (cost=5,505.22..5,512.22 rows=1 width=69) (actual time=38.998..39.018 rows=36 loops=1)

  • Join Filter: (((solicitacao.id_pessoa = 16,031) AND (tiposolicitacao.id_tipo_solicitacao = 2)) OR (((solicitacao.co_protocolo)::text = ANY ('{1046161555103273042019,1134561551453747032019,9912231549032337022019}'::text[ (...)
8. 0.004 38.985 ↑ 6.7 3 1

Merge Left Join (cost=5,505.22..5,505.37 rows=20 width=63) (actual time=38.984..38.985 rows=3 loops=1)

  • Merge Cond: ((solicitacao.co_protocolo)::text = (solicitacaolida.co_protocolo)::text)
9. 0.006 38.962 ↑ 6.7 3 1

Sort (cost=5,408.89..5,408.94 rows=20 width=62) (actual time=38.962..38.962 rows=3 loops=1)

  • Sort Key: solicitacao.co_protocolo
  • Sort Method: quicksort Memory: 25kB
10. 0.250 38.956 ↑ 6.7 3 1

Hash Right Join (cost=5,215.73..5,408.46 rows=20 width=62) (actual time=38.325..38.956 rows=3 loops=1)

  • Hash Cond: (sub_compartilhamento.id_solicitacao = solicitacao.id_solicitacao)
11. 2.537 38.651 ↑ 1.9 4,200 1

HashAggregate (cost=5,118.94..5,200.08 rows=8,114 width=16) (actual time=38.151..38.651 rows=4,200 loops=1)

  • Group Key: sub_compartilhamento.id_solicitacao, subtipo_solicitacao_interna.id_tipo_solicitacao, solicitacao_interna.id_subtipo_solicitacao, solicitacao_interna.id_tipo_status
12. 1.283 36.114 ↓ 1.0 8,368 1

Hash Left Join (cost=4,504.02..5,017.51 rows=8,114 width=16) (actual time=27.561..36.114 rows=8,368 loops=1)

  • Hash Cond: (solicitacao_interna.id_subtipo_solicitacao = subtipo_solicitacao_interna.id_subtipo_solicitacao)
13. 5.986 34.765 ↓ 1.0 8,368 1

Hash Left Join (cost=4,491.58..4,893.51 rows=8,114 width=12) (actual time=27.478..34.765 rows=8,368 loops=1)

  • Hash Cond: (sub_compartilhamento.id_solicitacao = solicitacao_interna.id_solicitacao)
14. 1.330 1.330 ↓ 1.0 8,368 1

Seq Scan on tb_compartilhamento sub_compartilhamento (cost=0.00..224.31 rows=8,114 width=4) (actual time=0.004..1.330 rows=8,368 loops=1)

  • Filter: (dt_exclusao IS NULL)
  • Rows Removed by Filter: 229
15. 12.969 27.449 ↑ 1.0 77,525 1

Hash (cost=3,522.37..3,522.37 rows=77,537 width=12) (actual time=27.449..27.449 rows=77,525 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,332kB
16. 14.480 14.480 ↑ 1.0 77,525 1

Seq Scan on tb_solicitacao solicitacao_interna (cost=0.00..3,522.37 rows=77,537 width=12) (actual time=0.003..14.480 rows=77,525 loops=1)

17. 0.031 0.066 ↑ 1.0 197 1

Hash (cost=9.97..9.97 rows=197 width=8) (actual time=0.066..0.066 rows=197 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
18. 0.035 0.035 ↑ 1.0 197 1

Seq Scan on tb_subtipo_solicitacao subtipo_solicitacao_interna (cost=0.00..9.97 rows=197 width=8) (actual time=0.003..0.035 rows=197 loops=1)

19. 0.002 0.055 ↑ 6.7 3 1

Hash (cost=96.55..96.55 rows=20 width=54) (actual time=0.055..0.055 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
20. 0.008 0.053 ↑ 6.7 3 1

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

  • Recheck Cond: ((id_pessoa = 16,031) OR ((co_protocolo)::text = ANY ('{1046161555103273042019,1134561551453747032019,9912231549032337022019}'::text[])))
  • Filter: ((id_solicitacao_pai IS NULL) AND (id_unidade_administrativa IS NULL))
  • Heap Blocks: exact=3
21. 0.001 0.045 ↓ 0.0 0 1

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

22. 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)
23. 0.037 0.037 ↑ 1.0 3 1

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

  • Index Cond: ((co_protocolo)::text = ANY ('{1046161555103273042019,1134561551453747032019,9912231549032337022019}'::text[]))
24. 0.010 0.019 ↓ 0.0 0 1

Sort (cost=96.33..96.35 rows=9 width=23) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: solicitacaolida.co_protocolo
  • Sort Method: quicksort Memory: 25kB
25. 0.000 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on tb_solicitacao_lida solicitacaolida (cost=4.61..96.19 rows=9 width=23) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: (id_pessoa = 16,031)
  • Filter: (NOT in_administrador)
26. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on ind_solicitacao_lida_id_pessoa (cost=0.00..4.61 rows=25 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (id_pessoa = 16,031)
27. 0.010 0.015 ↑ 1.0 12 3

Materialize (cost=0.00..1.18 rows=12 width=10) (actual time=0.003..0.005 rows=12 loops=3)

28. 0.005 0.005 ↑ 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.005 rows=12 loops=1)

29. 0.036 0.036 ↑ 1.0 1 36

Index Scan using idx_id_subtipo_solicitacao on tb_subtipo_solicitacao subtiposolicitacao (cost=0.14..2.17 rows=1 width=44) (actual time=0.001..0.001 rows=1 loops=36)

  • Index Cond: (id_subtipo_solicitacao = solicitacao.id_subtipo_solicitacao)
30. 0.006 0.006 ↑ 1.0 1 3

Index Scan using idx_id_tipo_status on tb_tipo_status tipostatus (cost=0.14..0.75 rows=1 width=220) (actual time=0.002..0.002 rows=1 loops=3)

  • Index Cond: (id_tipo_status = solicitacao.id_tipo_status)
31. 0.027 0.027 ↑ 1.0 1 3

Index Only Scan using ind_usuario_id_pessoa_id_usuario on tb_usuario u (cost=0.29..7.31 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=3)

  • Index Cond: (id_pessoa = solicitacao.id_pessoa)
  • Heap Fetches: 3
32. 0.000 2,021.747 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=711.78..1,023.60 rows=1 width=1,276) (actual time=2,021.747..2,021.747 rows=0 loops=1)

33. 0.001 2,021.747 ↓ 0.0 0 1

Nested Loop Left Join (cost=711.78..1,023.59 rows=1 width=1,276) (actual time=2,021.747..2,021.747 rows=0 loops=1)

  • Join Filter: ((t1.co_protocolo)::text = (solicitacaolida_1.co_protocolo)::text)
34. 0.001 2,021.746 ↓ 0.0 0 1

Hash Left Join (cost=707.17..927.27 rows=1 width=1,246) (actual time=2,021.746..2,021.746 rows=0 loops=1)

  • Hash Cond: (upper((t1.no_tipo_status)::text) = (ts.no_tipo_status)::text)
35. 29.713 2,021.745 ↓ 0.0 0 1

Hash Right Join (cost=705.86..925.94 rows=1 width=1,144) (actual time=2,021.745..2,021.745 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 ('{1046161555103273042019,1134561551453747032019,9912231549032337022019}'::text[])) AND (t1_1.id_tipo_solicitacao = 2)))
  • Rows Removed by Filter: 60,887
36. 3.631 890.766 ↑ 1.5 5,266 1

HashAggregate (cost=531.06..612.20 rows=8,114 width=32) (actual time=889.658..890.766 rows=5,266 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
37. 13.819 887.135 ↓ 1.0 8,368 1

Hash Right Join (cost=327.05..429.63 rows=8,114 width=32) (actual time=812.443..887.135 rows=8,368 loops=1)

  • Hash Cond: ((t1_1.co_protocolo)::text = (sub_compartilhamento_1.co_protocolo)::text)
38. 56.767 870.400 ↓ 61.4 61,403 1

Hash Left Join (cost=1.32..18.27 rows=1,000 width=130) (actual time=809.448..870.400 rows=61,403 loops=1)

  • Hash Cond: (lower((t1_1.no_tipo_status)::text) = lower((tipo_status_interna.no_tipo_status)::text))
39. 813.594 813.594 ↓ 61.4 61,403 1

Function Scan on dblink t1_1 (cost=0.00..10.00 rows=1,000 width=244) (actual time=809.395..813.594 rows=61,403 loops=1)

40. 0.026 0.039 ↓ 1.1 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
41. 0.013 0.013 ↓ 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.009..0.013 rows=15 loops=1)

42. 1.429 2.916 ↓ 1.0 8,368 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 431kB
43. 1.487 1.487 ↓ 1.0 8,368 1

Seq Scan on tb_compartilhamento sub_compartilhamento_1 (cost=0.00..224.31 rows=8,114 width=20) (actual time=0.004..1.487 rows=8,368 loops=1)

  • Filter: (dt_exclusao IS NULL)
  • Rows Removed by Filter: 229
44. 20.615 1,101.266 ↓ 3,044.3 60,887 1

Hash (cost=174.55..174.55 rows=20 width=1,112) (actual time=1,101.266..1,101.266 rows=60,887 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 7,145kB
45. 42.293 1,080.651 ↓ 3,044.3 60,887 1

Nested Loop (cost=0.29..174.55 rows=20 width=1,112) (actual time=781.540..1,080.651 rows=60,887 loops=1)

46. 792.746 792.746 ↓ 3,070.2 61,403 1

Function Scan on dblink t1 (cost=0.00..16.25 rows=20 width=1,620) (actual time=781.513..792.746 rows=61,403 loops=1)

  • Filter: ((id_tipo_solicitacao = 2) OR ((co_protocolo)::text = ANY ('{1046161555103273042019,1134561551453747032019,9912231549032337022019}'::text[])))
47. 245.612 245.612 ↑ 1.0 1 61,403

Index Scan using idx_no_login on tb_usuario u_1 (cost=0.29..7.91 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=61,403)

  • Index Cond: ((no_login)::text = (t1.reporter_id_pessoa)::text)
48. 0.000 0.000 ↓ 0.0 0

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

49. 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)

50. 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)
51. 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.695 ms
Execution time : 2,065.782 ms