explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bqeW

Settings
# exclusive inclusive rows x rows loops node
1. 4,131.378 17,038.924 ↑ 1.2 5,001,627 1

Gather (cost=257,990.98..1,119,896.67 rows=5,853,848 width=347) (actual time=3,418.243..17,038.924 rows=5,001,627 loops=1)

  • Output: ids_portos.codigo_entidade, eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, (to_date((eg.data_instalacao)::text, 'dd-MM-yyyy'::text)), eg.numero_serie, ieg.tipo_entida
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1267129, temp read=101816 written=101676
2. 2,511.801 12,907.546 ↑ 1.5 1,667,209 3

Hash Join (cost=257,890.98..1,044,184.47 rows=2,439,103 width=347) (actual time=3,917.949..12,907.546 rows=1,667,209 loops=3)

  • Output: ids_portos.codigo_entidade, eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, to_date((eg.data_instalacao)::text, 'dd-MM-yyyy'::text), eg.numero_serie, ieg.tipo_en
  • Hash Cond: (placas.codigo_entidade = idp.codigo_entidade)
  • Buffers: shared hit=3636518, temp read=250871 written=250451
  • Worker 0: actual time=3959.528..12084.566 rows=1357369 loops=1
  • Buffers: shared hit=1184246, temp read=74063 written=73923
  • Worker 1: actual time=4377.024..12180.859 rows=1386961 loops=1
  • Buffers: shared hit=1185143, temp read=74992 written=74852
3. 1,698.311 6,976.739 ↑ 1.3 1,667,209 3

Hash Join (cost=31,441.42..670,304.00 rows=2,084,227 width=111) (actual time=358.797..6,976.739 rows=1,667,209 loops=3)

  • Output: placas.versao_firmware, placas.estado_administrativo, placas.codigo_entidade, ids_portos.codigo_entidade, ids_portos.id_externo, ids_portos.tipo_entidade, ids_portos.object_id, ids_portos.codig
  • Inner Unique: true
  • Hash Cond: (ids_portos.codigo_placa = placas.codigo_entidade)
  • Buffers: shared hit=2001552, temp read=77531 written=77489
  • Worker 0: actual time=371.054..6392.883 rows=1357369 loops=1
  • Buffers: shared hit=639255, temp read=21622 written=21608
  • Worker 1: actual time=381.992..6420.599 rows=1386961 loops=1
  • Buffers: shared hit=640152, temp read=22039 written=22025
4. 949.372 4,921.718 ↑ 1.3 1,667,209 3

Merge Join (cost=1,041.48..544,327.72 rows=2,084,227 width=100) (actual time=0.474..4,921.718 rows=1,667,209 loops=3)

  • Output: ids_portos.codigo_entidade, ids_portos.id_externo, ids_portos.tipo_entidade, ids_portos.object_id, ids_portos.codigo_placa, portos.estado_administrativo, portos.nome_porto, portos.descric
  • Inner Unique: true
  • Merge Cond: (portos.codigo_entidade = ids_portos.codigo_entidade)
  • Buffers: shared hit=1979666
  • Worker 0: actual time=0.837..4877.114 rows=1357369 loops=1
  • Buffers: shared hit=631936
  • Worker 1: actual time=0.547..4867.017 rows=1386961 loops=1
  • Buffers: shared hit=632833
5. 995.252 2,594.865 ↑ 1.3 1,667,209 3

Merge Join (cost=27.65..321,371.13 rows=2,084,227 width=29) (actual time=0.289..2,594.865 rows=1,667,209 loops=3)

  • Output: portos.estado_administrativo, portos.nome_porto, portos.descricao, portos.codigo_entidade, recursos_portos.tipo_interface, recursos_portos.codigo_entidade
  • Inner Unique: true
  • Merge Cond: (portos.codigo_entidade = recursos_portos.codigo_entidade)
  • Buffers: shared hit=672698
  • Worker 0: actual time=0.494..2556.147 rows=1357369 loops=1
  • Buffers: shared hit=196275
  • Worker 1: actual time=0.346..2537.789 rows=1386961 loops=1
  • Buffers: shared hit=197183
6. 463.588 463.588 ↑ 1.3 1,667,209 3

Parallel Index Scan using portos_pkey on agorangmanager.portos (cost=0.43..126,033.27 rows=2,122,992 width=19) (actual time=0.027..463.588 rows=1,667,209 loops=3)

  • Output: portos.codigo_entidade, portos.nome_porto, portos.estado_administrativo, portos.id_dominio_sca, portos.descricao
  • Buffers: shared hit=193289
  • Worker 0: actual time=0.034..425.908 rows=1357369 loops=1
  • Buffers: shared hit=36468
  • Worker 1: actual time=0.034..434.634 rows=1386961 loops=1
  • Buffers: shared hit=37385
7. 1,136.025 1,136.025 ↑ 1.0 5,001,446 3

Index Scan using recursos_portos_pkey on agorangmanager.recursos_portos (cost=0.43..162,136.03 rows=5,002,144 width=10) (actual time=0.031..1,136.025 rows=5,001,446 loops=3)

  • Output: recursos_portos.codigo_entidade, recursos_portos.tipo_interface, recursos_portos.canais_disponiveis, recursos_portos.tipo_reserva, recursos_portos.lb_emissao, recursos_portos.
  • Buffers: shared hit=479409
  • Worker 0: actual time=0.041..1224.395 rows=5001627 loops=1
  • Buffers: shared hit=159807
  • Worker 1: actual time=0.041..1201.869 rows=5001174 loops=1
  • Buffers: shared hit=159798
8. 1,377.481 1,377.481 ↑ 1.0 5,001,447 3

Index Scan using ids_portos_pkey on agorangmanager.ids_portos (cost=0.43..191,268.05 rows=5,003,059 width=87) (actual time=0.028..1,377.481 rows=5,001,447 loops=3)

  • Output: ids_portos.codigo_entidade, ids_portos.tipo_entidade, ids_portos.object_id, ids_portos.codigo_placa, ids_portos.id_externo
  • Buffers: shared hit=1306968
  • Worker 0: actual time=0.037..1474.006 rows=5001628 loops=1
  • Buffers: shared hit=435661
  • Worker 1: actual time=0.037..1456.819 rows=5001175 loops=1
  • Buffers: shared hit=435650
9. 196.420 356.710 ↑ 1.0 845,442 3

Hash (cost=15,702.64..15,702.64 rows=845,464 width=11) (actual time=356.710..356.710 rows=845,442 loops=3)

  • Output: placas.versao_firmware, placas.estado_administrativo, placas.codigo_entidade
  • Buckets: 262144 Batches: 8 Memory Usage: 6988kB
  • Buffers: shared hit=21744, temp written=9750
  • Worker 0: actual time=368.315..368.315 rows=845442 loops=1
  • Buffers: shared hit=7248, temp written=3250
  • Worker 1: actual time=378.857..378.857 rows=845442 loops=1
  • Buffers: shared hit=7248, temp written=3250
10. 160.290 160.290 ↑ 1.0 845,442 3

Seq Scan on agorangmanager.placas (cost=0.00..15,702.64 rows=845,464 width=11) (actual time=0.024..160.290 rows=845,442 loops=3)

  • Output: placas.versao_firmware, placas.estado_administrativo, placas.codigo_entidade
  • Buffers: shared hit=21744
  • Worker 0: actual time=0.028..169.044 rows=845442 loops=1
  • Buffers: shared hit=7248
  • Worker 1: actual time=0.032..174.747 rows=845442 loops=1
  • Buffers: shared hit=7248
11. 713.738 3,419.006 ↑ 1.2 845,442 3

Hash (cost=180,257.30..180,257.30 rows=989,541 width=251) (actual time=3,419.006..3,419.006 rows=845,442 loops=3)

  • Output: eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, eg.data_instalacao, eg.numero_serie, eg.id_dominio_gerido, eg.id_area_central, eg.bastidor, eg.sub_bastidor
  • Buckets: 32768 Batches: 64 Memory Usage: 4125kB
  • Buffers: shared hit=1634960, temp written=86886
  • Worker 0: actual time=3588.020..3588.020 rows=845442 loops=1
  • Buffers: shared hit=544988, temp written=28962
  • Worker 1: actual time=3577.407..3577.407 rows=845442 loops=1
  • Buffers: shared hit=544988, temp written=28962
12. 390.319 2,705.268 ↑ 1.2 845,442 3

Merge Join (cost=189.91..180,257.30 rows=989,541 width=251) (actual time=0.131..2,705.268 rows=845,442 loops=3)

  • Output: eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, eg.data_instalacao, eg.numero_serie, eg.id_dominio_gerido, eg.id_area_central, eg.bastidor, eg.sub_ba
  • Inner Unique: true
  • Merge Cond: (eg.codigo_entidade = ieg.codigo_entidade)
  • Buffers: shared hit=1634960
  • Worker 0: actual time=0.170..2861.682 rows=845442 loops=1
  • Buffers: shared hit=544988
  • Worker 1: actual time=0.161..2851.308 rows=845442 loops=1
  • Buffers: shared hit=544988
13. 390.470 2,047.687 ↑ 1.0 845,442 3

Merge Left Join (cost=96.84..138,756.35 rows=845,516 width=227) (actual time=0.101..2,047.687 rows=845,442 loops=3)

  • Output: eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, eg.data_instalacao, eg.numero_serie, eg.id_dominio_gerido, eg.id_area_central, eg.bastidor, eg.
  • Inner Unique: true
  • Merge Cond: (eg.codigo_entidade = dei.codigo_entidade)
  • Buffers: shared hit=1498896
  • Worker 0: actual time=0.130..2167.100 rows=845442 loops=1
  • Buffers: shared hit=499633
  • Worker 1: actual time=0.124..2157.944 rows=845442 loops=1
  • Buffers: shared hit=499633
14. 553.688 1,367.020 ↑ 1.0 845,442 3

Merge Join (cost=96.42..102,057.56 rows=845,516 width=204) (actual time=0.070..1,367.020 rows=845,442 loops=3)

  • Output: eg.codigo_entidade, eg.estado_administrativo, eg.nome, eg.versao, eg.versao_firmware, eg.data_instalacao, eg.numero_serie, eg.id_dominio_gerido, eg.id_area_central, eg.bastido
  • Merge Cond: (eg.codigo_entidade = idp.codigo_elem_gerido)
  • Buffers: shared hit=1165852
  • Worker 0: actual time=0.090..1447.612 rows=845442 loops=1
  • Buffers: shared hit=388618
  • Worker 1: actual time=0.085..1437.696 rows=845442 loops=1
  • Buffers: shared hit=388618
15. 534.911 534.911 ↑ 1.2 844,424 3

Index Scan using elementos_geridos_pkey on agorangmanager.elementos_geridos eg (cost=0.42..56,868.71 rows=988,293 width=121) (actual time=0.021..534.911 rows=844,424 loops=3)

  • Output: eg.codigo_entidade, eg.id_dominio_gerido, eg.id_area_central, eg.id_modelo_equipamento, eg.nome, eg.data_instalacao, eg.localizacao, eg.versao, eg.numero_serie, eg.versa
  • Buffers: shared hit=1052927
  • Worker 0: actual time=0.026..586.134 rows=844424 loops=1
  • Buffers: shared hit=350976
  • Worker 1: actual time=0.024..586.907 rows=844424 loops=1
  • Buffers: shared hit=350976
16. 278.421 278.421 ↑ 1.0 845,443 3

Index Scan using ids_placas_index_2 on agorangmanager.ids_placas idp (cost=0.42..32,536.77 rows=845,516 width=83) (actual time=0.032..278.421 rows=845,443 loops=3)

  • Output: idp.codigo_entidade, idp.tipo_entidade, idp.object_id, idp.codigo_elem_gerido, idp.id_externo
  • Buffers: shared hit=112925
  • Worker 0: actual time=0.039..314.093 rows=845443 loops=1
  • Buffers: shared hit=37642
  • Worker 1: actual time=0.045..310.145 rows=845443 loops=1
  • Buffers: shared hit=37642
17. 290.197 290.197 ↑ 1.0 844,370 3

Index Scan using dados_equip_inserido_pkey on agorangmanager.dados_equip_inserido dei (cost=0.42..25,563.62 rows=844,432 width=31) (actual time=0.029..290.197 rows=844,370 loops=3)

  • Output: dei.codigo_entidade, dei.paric_index, dei.end_modem, dei.end_multiponto, dei.caminho
  • Buffers: shared hit=333044
  • Worker 0: actual time=0.038..326.700 rows=844370 loops=1
  • Buffers: shared hit=111015
  • Worker 1: actual time=0.037..324.200 rows=844370 loops=1
  • Buffers: shared hit=111015
18. 267.262 267.262 ↑ 1.0 844,425 3

Index Scan using ids_elementos_geridos_pkey on agorangmanager.ids_elementos_geridos ieg (cost=0.42..30,391.86 rows=844,450 width=40) (actual time=0.027..267.262 rows=844,425 loops=3)

  • Output: ieg.codigo_entidade, ieg.tipo_entidade, ieg.object_id, ieg.id_externo, ieg.manager_code
  • Buffers: shared hit=136064
  • Worker 0: actual time=0.036..299.335 rows=844425 loops=1
  • Buffers: shared hit=45355
  • Worker 1: actual time=0.034..299.204 rows=844425 loops=1
  • Buffers: shared hit=45355
Planning time : 4.859 ms
Execution time : 17,275.786 ms