explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jv9T

Settings
# exclusive inclusive rows x rows loops node
1. 26.468 119,233.830 ↓ 90.0 90 1

Nested Loop (cost=1,586,453.77..3,020,070.46 rows=1 width=794) (actual time=118,979.984..119,233.830 rows=90 loops=1)

  • Join Filter: (login.id_pessoa = pessoa.id_pessoa)
  • Rows Removed by Join Filter: 200851
2. 181.822 181.822 ↑ 1.0 1 1

Index Scan using pk_login on login (cost=0.00..8.36 rows=1 width=197) (actual time=181.787..181.822 rows=1 loops=1)

  • Index Cond: (id_login = 5357)
3. 2,834.631 119,025.540 ↓ 2.7 200,941 1

HashAggregate (cost=1,586,453.77..3,018,389.49 rows=74,338 width=533) (actual time=118,792.574..119,025.540 rows=200,941 loops=1)

4.          

CTE unidade_relacionada

5. 0.557 11.000 ↓ 3.5 637 1

HashAggregate (cost=23.05..24.85 rows=180 width=8) (actual time=10.636..11.000 rows=637 loops=1)

6. 0.061 10.443 ↓ 3.5 638 1

Append (cost=8.05..22.15 rows=180 width=8) (actual time=10.196..10.443 rows=638 loops=1)

7. 0.093 10.319 ↓ 6.1 548 1

Hash Left Join (cost=8.05..13.43 rows=90 width=8) (actual time=10.194..10.319 rows=548 loops=1)

  • Hash Cond: (unidade_principal.id_unidade = COALESCE(unidade_relacionada.id_unidade_principal, unidade_relacionada.id_unidade))
8. 0.062 10.180 ↑ 1.0 90 1

Hash Left Join (cost=4.03..8.16 rows=90 width=8) (actual time=10.138..10.180 rows=90 loops=1)

  • Hash Cond: (COALESCE(senai.unidade.id_unidade_principal, senai.unidade.id_unidade) = unidade_principal.id_unidade)
9. 10.058 10.058 ↑ 1.0 90 1

Seq Scan on unidade (cost=0.00..2.90 rows=90 width=8) (actual time=10.047..10.058 rows=90 loops=1)

10. 0.016 0.060 ↑ 1.0 90 1

Hash (cost=2.90..2.90 rows=90 width=4) (actual time=0.060..0.060 rows=90 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
11. 0.044 0.044 ↑ 1.0 90 1

Seq Scan on unidade unidade_principal (cost=0.00..2.90 rows=90 width=4) (actual time=0.007..0.044 rows=90 loops=1)

12. 0.025 0.046 ↑ 1.0 90 1

Hash (cost=2.90..2.90 rows=90 width=8) (actual time=0.046..0.046 rows=90 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
13. 0.021 0.021 ↑ 1.0 90 1

Seq Scan on unidade unidade_relacionada (cost=0.00..2.90 rows=90 width=8) (actual time=0.003..0.021 rows=90 loops=1)

14. 0.030 0.063 ↑ 1.0 90 1

Nested Loop (cost=0.00..6.93 rows=90 width=8) (actual time=0.018..0.063 rows=90 loops=1)

15. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on unidade unidade_gestao (cost=0.00..3.12 rows=1 width=4) (actual time=0.008..0.023 rows=1 loops=1)

  • Filter: ((tipo)::text = 'Gestão'::text)
  • Rows Removed by Filter: 89
16. 0.010 0.010 ↑ 1.0 90 1

Seq Scan on unidade unidade_relacionada (cost=0.00..2.90 rows=90 width=4) (actual time=0.003..0.010 rows=90 loops=1)

17.          

CTE unidade_regional

18. 0.063 0.885 ↑ 1.2 75 1

Hash Join (cost=2.69..5.52 rows=89 width=8) (actual time=0.848..0.885 rows=75 loops=1)

  • Hash Cond: (lotacao.id_regional = regional_unidade.id_regional)
19. 0.763 0.763 ↑ 1.0 13 1

Seq Scan on regional_unidade lotacao (cost=0.00..1.75 rows=13 width=8) (actual time=0.756..0.763 rows=13 loops=1)

  • Filter: lotacao
  • Rows Removed by Filter: 62
20. 0.028 0.059 ↑ 1.0 75 1

Hash (cost=1.75..1.75 rows=75 width=8) (actual time=0.059..0.059 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
21. 0.031 0.031 ↑ 1.0 75 1

Seq Scan on regional_unidade (cost=0.00..1.75 rows=75 width=8) (actual time=0.006..0.031 rows=75 loops=1)

22. 9,691.185 116,190.909 ↓ 9.8 729,972 1

Hash Join (cost=100,569.00..1,583,821.56 rows=74,338 width=533) (actual time=7,405.947..116,190.909 rows=729,972 loops=1)

  • Hash Cond: (colaborador.id_unidade = unidade_relacionada.id_unidade)
  • Join Filter: ((senai.unidade.id_unidade = unidade_relacionada.id_unidade_relacionada) OR (senai.unidade.id_unidade = unidade_colaborador.id_unidade) OR (senai.unidade.id_unidade = unidade_regional.id_unidade))
  • Rows Removed by Join Filter: 34030728
23. 126.626 7,891.059 ↓ 1.3 39,105 1

Hash Right Join (cost=100,154.99..101,513.52 rows=30,001 width=67) (actual time=7,323.379..7,891.059 rows=39,105 loops=1)

  • Hash Cond: (unidade_colaborador.id_colaborador = colaborador.id_colaborador)
24. 443.588 443.588 ↑ 1.0 30,001 1

Seq Scan on unidade_colaborador (cost=0.00..646.01 rows=30,001 width=8) (actual time=2.507..443.588 rows=30,001 loops=1)

25. 11.452 7,320.845 ↓ 1.1 19,191 1

Hash (cost=99,929.33..99,929.33 rows=18,053 width=63) (actual time=7,320.845..7,320.845 rows=19,191 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1358kB
26. 19.471 7,309.393 ↓ 1.1 19,191 1

Hash Left Join (cost=35,798.67..99,929.33 rows=18,053 width=63) (actual time=7,234.684..7,309.393 rows=19,191 loops=1)

  • Hash Cond: (colaborador.id_unidade = unidade_regional.id_unidade_lotacao)
  • Join Filter: (alternatives: SubPlan 5 or hashed SubPlan 6)
  • Rows Removed by Join Filter: 64380
27. 40.036 7,287.955 ↑ 1.0 18,053 1

Hash Join (cost=35,795.78..36,718.37 rows=18,053 width=63) (actual time=7,233.707..7,287.955 rows=18,053 loops=1)

  • Hash Cond: (colaborador.id_pessoa = pessoa.id_pessoa)
28. 18.232 18.232 ↑ 1.0 18,053 1

Seq Scan on colaborador (cost=0.00..561.53 rows=18,053 width=20) (actual time=3.108..18.232 rows=18,053 loops=1)

29. 363.152 7,229.687 ↑ 1.0 768,879 1

Hash (cost=26,184.79..26,184.79 rows=768,879 width=47) (actual time=7,229.687..7,229.687 rows=768,879 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 44296kB
30. 6,866.535 6,866.535 ↑ 1.0 768,879 1

Seq Scan on pessoa (cost=0.00..26,184.79 rows=768,879 width=47) (actual time=4.532..6,866.535 rows=768,879 loops=1)

31. 0.022 0.950 ↑ 1.2 75 1

Hash (cost=1.78..1.78 rows=89 width=8) (actual time=0.950..0.950 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
32. 0.928 0.928 ↑ 1.2 75 1

CTE Scan on unidade_regional (cost=0.00..1.78 rows=89 width=8) (actual time=0.852..0.928 rows=75 loops=1)

33.          

SubPlan (forHash Left Join)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on nucleo (cost=0.00..3.49 rows=1 width=0) (never executed)

  • Filter: (compartilhado AND (id_nucleo = colaborador.id_nucleo))
35. 1.017 1.017 ↑ 1.0 1 1

Seq Scan on nucleo (cost=0.00..3.19 rows=1 width=4) (actual time=1.016..1.017 rows=1 loops=1)

  • Filter: compartilhado
  • Rows Removed by Filter: 118
36. 24.647 62.445 ↓ 3.5 57,330 1

Hash (cost=211.51..211.51 rows=16,200 width=486) (actual time=62.445..62.445 rows=57,330 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 6166kB
37. 13.075 37.798 ↓ 3.5 57,330 1

Nested Loop (cost=1.04..211.51 rows=16,200 width=486) (actual time=19.784..37.798 rows=57,330 loops=1)

38. 11.346 11.346 ↓ 3.5 637 1

CTE Scan on unidade_relacionada (cost=0.00..3.60 rows=180 width=8) (actual time=10.641..11.346 rows=637 loops=1)

39. 4.158 13.377 ↑ 1.0 90 637

Materialize (cost=1.04..5.63 rows=90 width=478) (actual time=0.014..0.021 rows=90 loops=637)

40. 0.143 9.219 ↑ 1.0 90 1

Hash Left Join (cost=1.04..5.18 rows=90 width=478) (actual time=9.136..9.219 rows=90 loops=1)

  • Hash Cond: (senai.unidade.id_entidade = entidade.id_entidade)
41. 0.016 0.016 ↑ 1.0 90 1

Seq Scan on unidade (cost=0.00..2.90 rows=90 width=64) (actual time=0.002..0.016 rows=90 loops=1)

42. 0.008 9.060 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=422) (actual time=9.060..9.060 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
43. 9.052 9.052 ↑ 1.0 2 1

Seq Scan on entidade (cost=0.00..1.02 rows=2 width=422) (actual time=9.049..9.052 rows=2 loops=1)

44.          

SubPlan (forHash Join)

45. 11,679.552 44,528.292 ↑ 1.0 1 729,972

Aggregate (cost=7.38..7.39 rows=1 width=418) (actual time=0.061..0.061 rows=1 loops=729,972)

46. 2,189.916 32,848.740 ↓ 1.5 3 729,972

Nested Loop (cost=0.00..6.88 rows=2 width=418) (actual time=0.026..0.045 rows=3 loops=729,972)

  • Join Filter: (senai.negocio.id_negocio = senai.unidade_negocio.id_negocio)
  • Rows Removed by Join Filter: 10
47. 1,459.944 1,459.944 ↑ 1.0 5 729,972

Seq Scan on negocio (cost=0.00..1.05 rows=5 width=422) (actual time=0.002..0.002 rows=5 loops=729,972)

48. 4,379.832 29,198.880 ↓ 1.5 3 3,649,860

Materialize (cost=0.00..5.69 rows=2 width=4) (actual time=0.004..0.008 rows=3 loops=3,649,860)

49. 24,819.048 24,819.048 ↓ 1.5 3 729,972

Seq Scan on unidade_negocio (cost=0.00..5.68 rows=2 width=4) (actual time=0.019..0.034 rows=3 loops=729,972)

  • Filter: (id_unidade = senai.unidade.id_unidade)
  • Rows Removed by Filter: 211
50. 11,679.552 54,017.928 ↑ 1.0 1 729,972

Aggregate (cost=11.85..11.86 rows=1 width=418) (actual time=0.074..0.074 rows=1 loops=729,972)

51. 9,384.068 42,338.376 ↓ 3.0 3 729,972

Nested Loop (cost=0.00..11.60 rows=1 width=418) (actual time=0.029..0.058 rows=3 loops=729,972)

  • Join Filter: (senai.negocio.id_negocio = senai.unidade_negocio.id_negocio)
  • Rows Removed by Join Filter: 10
52. 29,198.880 29,198.880 ↓ 3.0 3 729,972

Seq Scan on unidade_negocio (cost=0.00..10.49 rows=1 width=4) (actual time=0.022..0.040 rows=3 loops=729,972)

  • Filter: ((id_unidade = senai.unidade.id_unidade) AND ((('now'::cstring)::date)::timestamp without time zone >= inicio) AND ((('now'::cstring)::date)::timestamp without time zone <= COALESCE((termino)::timestamp without time zone, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 211
53. 3,755.428 3,755.428 ↑ 1.0 5 1,877,714

Seq Scan on negocio (cost=0.00..1.05 rows=5 width=422) (actual time=0.001..0.002 rows=5 loops=1,877,714)