explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PpDP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,071.814 ↓ 0.0 0 1

Subquery Scan on tmp (cost=5,502.50..6,542.65 rows=2 width=1,255) (actual time=2,071.814..2,071.814 rows=0 loops=1)

2. 0.002 2,071.814 ↓ 0.0 0 1

Append (cost=5,502.50..6,542.62 rows=2 width=800) (actual time=2,071.814..2,071.814 rows=0 loops=1)

3. 0.004 2.014 ↓ 0.0 0 1

WindowAgg (cost=5,502.50..5,519.02 rows=1 width=325) (actual time=2.014..2.014 rows=0 loops=1)

4. 0.000 2.010 ↓ 0.0 0 1

Nested Loop (cost=5,502.50..5,518.99 rows=1 width=325) (actual time=2.010..2.010 rows=0 loops=1)

5. 0.000 2.010 ↓ 0.0 0 1

Nested Loop (cost=5,502.22..5,511.88 rows=1 width=321) (actual time=2.010..2.010 rows=0 loops=1)

6. 0.000 2.010 ↓ 0.0 0 1

Nested Loop (cost=5,502.08..5,511.11 rows=1 width=105) (actual time=2.010..2.010 rows=0 loops=1)

  • Join Filter: (tiposolicitacao.id_tipo_solicitacao = subtiposolicitacao.id_tipo_solicitacao)
7. 0.000 2.010 ↓ 0.0 0 1

Nested Loop (cost=5,501.94..5,508.94 rows=1 width=69) (actual time=2.010..2.010 rows=0 loops=1)

  • Join Filter: (((solicitacao.id_pessoa = 16,031) AND (tiposolicitacao.id_tipo_solicitacao = 2)) OR (((solicitacao.co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])) AND (subt (...)
8. 0.001 2.010 ↓ 0.0 0 1

Merge Left Join (cost=5,501.94..5,502.09 rows=20 width=63) (actual time=2.010..2.010 rows=0 loops=1)

  • Merge Cond: ((solicitacao.co_protocolo)::text = (solicitacaolida.co_protocolo)::text)
9. 0.015 2.009 ↓ 0.0 0 1

Sort (cost=5,405.61..5,405.66 rows=20 width=62) (actual time=2.009..2.009 rows=0 loops=1)

  • Sort Key: solicitacao.co_protocolo
  • Sort Method: quicksort Memory: 25kB
10. 0.021 1.994 ↓ 0.0 0 1

Hash Right Join (cost=5,212.45..5,405.17 rows=20 width=62) (actual time=1.994..1.994 rows=0 loops=1)

  • Hash Cond: (sub_compartilhamento.id_solicitacao = solicitacao.id_solicitacao)
11. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,115.65..5,196.79 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,500.73..5,014.23 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,488.30..4,890.23 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,519.80..3,519.80 rows=77,480 width=12) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_solicitacao solicitacao_interna (cost=0.00..3,519.80 rows=77,480 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 1.973 ↓ 0.0 0 1

Hash (cost=96.54..96.54 rows=20 width=54) (actual time=1.973..1.973 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
20. 0.000 1.973 ↓ 0.0 0 1

Bitmap Heap Scan on tb_solicitacao solicitacao (cost=17.71..96.54 rows=20 width=54) (actual time=1.973..1.973 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))
21. 0.002 1.973 ↓ 0.0 0 1

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

22. 0.048 0.048 ↓ 0.0 0 1

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

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

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

  • Index Cond: ((co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[]))
24. 0.000 0.000 ↓ 0.0 0

Sort (cost=96.33..96.35 rows=9 width=23) (never executed)

  • Sort Key: solicitacaolida.co_protocolo
25. 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)
26. 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)
27. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.18 rows=12 width=10) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_tipo_solicitacao tiposolicitacao (cost=0.00..1.12 rows=12 width=10) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_id_subtipo_solicitacao on tb_subtipo_solicitacao subtiposolicitacao (cost=0.14..2.17 rows=1 width=44) (never executed)

  • Index Cond: (id_subtipo_solicitacao = solicitacao.id_subtipo_solicitacao)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_id_tipo_status on tb_tipo_status tipostatus (cost=0.14..0.75 rows=1 width=220) (never executed)

  • Index Cond: (id_tipo_status = solicitacao.id_tipo_status)
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ind_usuario_id_pessoa_id_usuario on tb_usuario u (cost=0.29..7.10 rows=1 width=8) (never executed)

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

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

33. 0.001 2,069.798 ↓ 0.0 0 1

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

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

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

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

Hash Right Join (cost=705.86..925.94 rows=1 width=1,144) (actual time=2,069.797..2,069.797 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,807
36. 3.562 888.886 ↑ 1.5 5,261 1

HashAggregate (cost=531.06..612.20 rows=8,114 width=32) (actual time=887.827..888.886 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
37. 14.052 885.324 ↓ 1.0 8,361 1

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

  • Hash Cond: ((t1_1.co_protocolo)::text = (sub_compartilhamento_1.co_protocolo)::text)
38. 56.742 867.282 ↓ 61.3 61,323 1

Hash Left Join (cost=1.32..18.27 rows=1,000 width=130) (actual time=806.362..867.282 rows=61,323 loops=1)

  • Hash Cond: (lower((t1_1.no_tipo_status)::text) = lower((tipo_status_interna.no_tipo_status)::text))
39. 810.422 810.422 ↓ 61.3 61,323 1

Function Scan on dblink t1_1 (cost=0.00..10.00 rows=1,000 width=244) (actual time=806.231..810.422 rows=61,323 loops=1)

40. 0.106 0.118 ↓ 1.1 15 1

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

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

42. 1.479 3.990 ↓ 1.0 8,361 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 431kB
43. 2.511 2.511 ↓ 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.014..2.511 rows=8,361 loops=1)

  • Filter: (dt_exclusao IS NULL)
  • Rows Removed by Filter: 229
44. 21.190 1,151.947 ↓ 3,040.3 60,807 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 7,136kB
45. 0.000 1,130.757 ↓ 3,040.3 60,807 1

Nested Loop (cost=0.29..174.55 rows=20 width=1,112) (actual time=815.542..1,130.757 rows=60,807 loops=1)

46. 827.332 827.332 ↓ 3,066.2 61,323 1

Function Scan on dblink t1 (cost=0.00..16.25 rows=20 width=1,620) (actual time=815.472..827.332 rows=61,323 loops=1)

  • Filter: ((id_tipo_solicitacao = 2) OR ((co_protocolo)::text = ANY ('{1203981551301262022019,3078051563994641072019,CAT-15329}'::text[])))
47. 306.615 306.615 ↑ 1.0 1 61,323

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.005 rows=1 loops=61,323)

  • 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 : 6.273 ms
Execution time : 2,080.850 ms