explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d6Uz

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 3,544.259 ↓ 36.5 73 1

GroupAggregate (cost=43,124.27..43,124.36 rows=2 width=234) (actual time=3,544.202..3,544.259 rows=73 loops=1)

  • Group Key: t0_.id_pessoa, t0_.no_pessoa, t0_.no_sobrenome, ch.no_cargo, ch.no_funcao, t2_.no_situacao_cadastro, t3_.sg_pais, t3_.no_pais, t4_.id_tipo_carteira, kk1.dt_alteracao, kk2.no_registro
2. 0.557 3,544.202 ↓ 68.0 136 1

Sort (cost=43,124.27..43,124.28 rows=2 width=234) (actual time=3,544.198..3,544.202 rows=136 loops=1)

  • Sort Key: t0_.id_pessoa, t0_.no_pessoa, t0_.no_sobrenome, ch.no_cargo, ch.no_funcao, t2_.no_situacao_cadastro, t3_.sg_pais, t3_.no_pais, t4_.id_tipo_carteira, kk1.dt_alteracao, kk2.no_registro
  • Sort Method: quicksort Memory: 51kB
3. 25.378 3,543.645 ↓ 68.0 136 1

Hash Left Join (cost=3,126.15..43,124.26 rows=2 width=234) (actual time=15.137..3,543.645 rows=136 loops=1)

  • Hash Cond: (t9_.id_pais = t3_.id_pais)
  • Filter: (((t0_.no_registro_ascii)::text = '34.543-x'::text) OR ((t5_.no_registro_ascii)::text = '34.543-x'::text) OR ((((t0_.no_pessoa_ascii)::text || ' '::text) || (t0_.no_sobrenome_ascii)::text) = 'lcdi'::text) OR ((((t5_.no_pessoa_ascii):: (...)
  • Rows Removed by Filter: 3,518
4. 1.585 3,458.385 ↓ 96.2 3,654 1

Hash Left Join (cost=3,118.29..41,617.85 rows=38 width=325) (actual time=11.974..3,458.385 rows=3,654 loops=1)

  • Hash Cond: (t0_.id_pessoa = kk1.id_pessoa)
5. 1.192 3,456.794 ↓ 96.2 3,654 1

Nested Loop Left Join (cost=3,109.96..41,609.37 rows=38 width=317) (actual time=11.961..3,456.794 rows=3,654 loops=1)

6. 2.654 3,448.258 ↓ 64.4 2,448 1

Hash Join (cost=3,109.68..41,575.63 rows=38 width=282) (actual time=11.944..3,448.258 rows=2,448 loops=1)

  • Hash Cond: (t0_.id_situacao_cadastro = t2_.id_situacao_cadastro)
  • Join Filter: ((t2_.id_situacao_cadastro <> ALL ('{1,2,4,5,6}'::integer[])) OR ((t2_.id_situacao_cadastro = ANY ('{2,4,5,6}'::integer[])) AND (hashed SubPlan 2)))
  • Rows Removed by Join Filter: 4
7. 3.894 3,445.583 ↓ 39.2 2,470 1

Hash Left Join (cost=3,107.29..41,571.96 rows=63 width=168) (actual time=11.926..3,445.583 rows=2,470 loops=1)

  • Hash Cond: (t0_.id_pessoa = kk2.id_pessoa)
8. 2.472 3,431.208 ↓ 39.2 2,470 1

Nested Loop Left Join (cost=2.22..38,465.83 rows=63 width=158) (actual time=1.417..3,431.208 rows=2,470 loops=1)

9. 7.999 3,423.796 ↓ 39.2 2,470 1

Nested Loop (cost=1.94..38,445.26 rows=63 width=158) (actual time=1.412..3,423.796 rows=2,470 loops=1)

  • Join Filter: (t13_.id_categoria_funcional = t12_.id_categoria_funcional)
  • Rows Removed by Join Filter: 71,630
10. 2.325 3,413.327 ↓ 39.2 2,470 1

Nested Loop (cost=1.94..38,415.54 rows=63 width=166) (actual time=1.405..3,413.327 rows=2,470 loops=1)

11. 1.378 3,398.567 ↓ 20.1 2,487 1

Nested Loop (cost=1.66..38,240.31 rows=124 width=95) (actual time=1.396..3,398.567 rows=2,487 loops=1)

12. 0.638 1.939 ↓ 10.0 2,515 1

Nested Loop (cost=1.38..54.01 rows=251 width=16) (actual time=0.017..1.939 rows=2,515 loops=1)

13. 0.052 0.071 ↓ 10.0 30 1

Hash Join (cost=1.10..2.54 rows=3 width=8) (actual time=0.009..0.071 rows=30 loops=1)

  • Hash Cond: (t13_.id_tipo_carteira = t4_.id_tipo_carteira)
14. 0.015 0.015 ↑ 1.0 30 1

Seq Scan on tb_tipo_carteira_categoria t13_ (cost=0.00..1.30 rows=30 width=8) (actual time=0.002..0.015 rows=30 loops=1)

15. 0.002 0.004 ↓ 2.0 6 1

Hash (cost=1.06..1.06 rows=3 width=4) (actual time=0.004..0.004 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
16. 0.002 0.002 ↓ 2.0 6 1

Seq Scan on tb_tipo_carteira t4_ (cost=0.00..1.06 rows=3 width=4) (actual time=0.001..0.002 rows=6 loops=1)

  • Filter: in_ativo
17. 1.230 1.230 ↑ 1.0 84 30

Index Scan using idx_tb_chegada_003 on tb_chegada t10_ (cost=0.28..16.29 rows=87 width=12) (actual time=0.005..0.041 rows=84 loops=30)

  • Index Cond: (id_categoria_funcional = t13_.id_categoria_funcional)
18. 221.563 3,395.250 ↑ 1.0 1 2,515

Index Scan using pk_chegada on tb_chegada ch (cost=0.28..152.13 rows=1 width=87) (actual time=1.349..1.350 rows=1 loops=2,515)

  • Index Cond: (id_chegada = t10_.id_chegada)
  • Filter: (in_ativo AND (SubPlan 1))
  • Rows Removed by Filter: 0
19.          

SubPlan (for Index Scan)

20. 1,523.170 3,173.687 ↑ 2.0 1,260 2,497

HashAggregate (cost=135.93..161.05 rows=2,512 width=8) (actual time=1.148..1.271 rows=1,260 loops=2,497)

  • Group Key: tb_chegada.id_pessoa
21. 1,221.033 1,650.517 ↑ 1.0 2,529 2,497

Result (cost=0.00..123.29 rows=2,529 width=8) (actual time=0.005..0.661 rows=2,529 loops=2,497)

  • One-Time Filter: ch.in_ativo
22. 429.484 429.484 ↑ 1.0 2,529 2,497

Seq Scan on tb_chegada (cost=0.00..123.29 rows=2,529 width=8) (actual time=0.005..0.172 rows=2,529 loops=2,497)

23. 12.435 12.435 ↑ 1.0 1 2,487

Index Scan using idx_tb_pessoa_003 on tb_pessoa t0_ (cost=0.28..1.40 rows=1 width=75) (actual time=0.004..0.005 rows=1 loops=2,487)

  • Index Cond: ((id_titular IS NULL) AND (id_pessoa = ch.id_pessoa))
  • Filter: in_ativo
  • Rows Removed by Filter: 0
24. 2.464 2.470 ↑ 1.0 30 2,470

Materialize (cost=0.00..1.45 rows=30 width=4) (actual time=0.000..0.001 rows=30 loops=2,470)

25. 0.006 0.006 ↑ 1.0 30 1

Seq Scan on tb_categoria_funcional t12_ (cost=0.00..1.30 rows=30 width=4) (actual time=0.002..0.006 rows=30 loops=1)

26. 4.940 4.940 ↑ 1.0 1 2,470

Index Scan using pk_tb_organizacao on tb_organizacao t9_ (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,470)

  • Index Cond: (id_organizacao = t0_.id_organizacao)
27. 0.646 10.481 ↑ 1.0 4,557 1

Hash (cost=3,048.10..3,048.10 rows=4,558 width=14) (actual time=10.481..10.481 rows=4,557 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 214kB
28. 0.381 9.835 ↑ 1.0 4,558 1

Subquery Scan on kk2 (cost=2,978.59..3,048.10 rows=4,558 width=14) (actual time=8.722..9.835 rows=4,558 loops=1)

29. 0.531 9.454 ↑ 1.0 4,558 1

Unique (cost=2,978.59..3,002.52 rows=4,558 width=22) (actual time=8.721..9.454 rows=4,558 loops=1)

30. 1.695 8.923 ↑ 1.0 4,786 1

Sort (cost=2,978.59..2,990.55 rows=4,786 width=22) (actual time=8.720..8.923 rows=4,786 loops=1)

  • Sort Key: t8_.id_pessoa, t7_.dt_expedicao
  • Sort Method: quicksort Memory: 566kB
31. 3.304 7.228 ↑ 1.0 4,786 1

Hash Right Join (cost=380.70..2,686.05 rows=4,786 width=22) (actual time=2.672..7.228 rows=4,786 loops=1)

  • Hash Cond: (t7_.id_solicitacao = t8_.id_solicitacao)
32. 1.848 1.848 ↑ 1.0 4,778 1

Seq Scan on tb_solicitacao_recibo t7_ (cost=0.00..2,215.78 rows=4,778 width=34) (actual time=0.578..1.848 rows=4,778 loops=1)

33. 0.802 2.076 ↑ 1.0 4,786 1

Hash (cost=320.88..320.88 rows=4,786 width=20) (actual time=2.076..2.076 rows=4,786 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 244kB
34. 1.274 1.274 ↑ 1.0 4,786 1

Seq Scan on tb_solicitacao t8_ (cost=0.00..320.88 rows=4,786 width=20) (actual time=0.003..1.274 rows=4,786 loops=1)

  • Filter: in_ativo
  • Rows Removed by Filter: 2
35. 0.002 0.009 ↓ 1.3 9 1

Hash (cost=1.21..1.21 rows=7 width=122) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
36. 0.007 0.007 ↓ 1.3 9 1

Seq Scan on tb_situacao_cadastro t2_ (cost=0.00..1.21 rows=7 width=122) (actual time=0.005..0.007 rows=9 loops=1)

  • Filter: ((id_situacao_cadastro <> ALL ('{1,2,4,5,6}'::integer[])) OR (id_situacao_cadastro = ANY ('{2,4,5,6}'::integer[])))
  • Rows Removed by Filter: 1
37.          

SubPlan (for Hash Join)

38. 0.012 0.012 ↓ 4.0 4 1

Seq Scan on tb_unidade_administrativa_tipo_solicitacao t14_ (cost=0.00..1.09 rows=1 width=4) (actual time=0.012..0.012 rows=4 loops=1)

  • Filter: ((id_unidade_administrativa = 71) AND (id_tipo_solicitacao = 1))
  • Rows Removed by Filter: 2
39. 7.344 7.344 ↑ 2.0 1 2,448

Index Scan using idx_tb_pessoa_003 on tb_pessoa t5_ (cost=0.28..0.87 rows=2 width=39) (actual time=0.002..0.003 rows=1 loops=2,448)

  • Index Cond: (t0_.id_pessoa = id_titular)
  • Filter: in_ativo
  • Rows Removed by Filter: 0
40. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=8.31..8.31 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
41. 0.002 0.006 ↓ 0.0 0 1

Subquery Scan on kk1 (cost=0.29..8.31 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=1)

42. 0.000 0.004 ↓ 0.0 0 1

Unique (cost=0.29..8.30 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)

43. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using idx_tb_historico_situacao_cad_001 on tb_historico_situacao_cadastro t6_ (cost=0.29..8.30 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id_situacao_cadastro = 7)
  • Heap Fetches: 0
44. 0.043 0.076 ↑ 1.0 216 1

Hash (cost=5.16..5.16 rows=216 width=18) (actual time=0.076..0.076 rows=216 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
45. 0.033 0.033 ↑ 1.0 216 1

Seq Scan on tb_pais t3_ (cost=0.00..5.16 rows=216 width=18) (actual time=0.007..0.033 rows=216 loops=1)

46.          

SubPlan (for Hash Left Join)

47. 3.518 7.036 ↓ 0.0 0 3,518

Limit (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=3,518)

48. 3.518 3.518 ↓ 0.0 0 3,518

Index Only Scan using idx_tb_historico_situacao_cad_001 on tb_historico_situacao_cadastro t15_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3,518)

  • Index Cond: ((id_situacao_cadastro = 7) AND (id_pessoa = t0_.id_pessoa))
  • Heap Fetches: 0
49. 3.518 35.180 ↑ 1.0 1 3,518

Limit (cost=16.62..16.63 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=3,518)

50. 7.036 31.662 ↑ 1.0 1 3,518

Sort (cost=16.62..16.63 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=3,518)

  • Sort Key: t16_.dt_expedicao
  • Sort Method: quicksort Memory: 25kB
51. 3.044 24.626 ↑ 1.0 1 3,518

Nested Loop Left Join (cost=0.56..16.61 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=3,518)

52. 10.554 10.554 ↑ 1.0 1 3,518

Index Only Scan using idx_tb_solicitacao_005 on tb_solicitacao t17_ (cost=0.28..8.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,518)

  • Index Cond: ((id_pessoa = t0_.id_pessoa) AND (in_ativo = true))
  • Filter: in_ativo
  • Heap Fetches: 3,676
53. 11.028 11.028 ↑ 1.0 1 3,676

Index Scan using idx_tb_solicitacao_recibo_004 on tb_solicitacao_recibo t16_ (cost=0.28..8.30 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=3,676)

  • Index Cond: (t17_.id_solicitacao = id_solicitacao)
54. 3.518 17.590 ↑ 1.0 1 3,518

Limit (cost=16.62..16.63 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=3,518)

55. 0.000 14.072 ↑ 1.0 1 3,518

Sort (cost=16.62..16.63 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=3,518)

  • Sort Key: t18_.dt_expedicao
  • Sort Method: quicksort Memory: 25kB
56. 4.290 14.072 ↑ 1.0 1 3,518

Nested Loop Left Join (cost=0.56..16.61 rows=1 width=18) (actual time=0.003..0.004 rows=1 loops=3,518)

57. 3.518 3.518 ↑ 1.0 1 3,518

Index Only Scan using idx_tb_solicitacao_005 on tb_solicitacao t19_ (cost=0.28..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=3,518)

  • Index Cond: ((id_pessoa = t5_.id_pessoa) AND (in_ativo = true))
  • Filter: in_ativo
  • Heap Fetches: 2,088
58. 6.264 6.264 ↑ 1.0 1 2,088

Index Scan using idx_tb_solicitacao_recibo_004 on tb_solicitacao_recibo t18_ (cost=0.28..8.30 rows=1 width=34) (actual time=0.002..0.003 rows=1 loops=2,088)

  • Index Cond: (t19_.id_solicitacao = id_solicitacao)
Planning time : 2.811 ms
Execution time : 3,544.544 ms