explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k8vw

Settings
# exclusive inclusive rows x rows loops node
1. 386.944 8,403.540 ↓ 1.0 844,370 1

Gather (cost=59,147.23..467,778.34 rows=841,041 width=512) (actual time=1,179.255..8,403.540 rows=844,370 loops=1)

  • Output: ont.codigo_entidade, id_ont.manager_code, ids_placas.codigo_entidade, ids_placas.id_externo, placas.estado_administrativo, ids_placas.tipo_entidade, ids_placas.object_id,
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=1984942
2. 185.448 8,016.596 ↑ 1.3 211,092 4

Hash Join (cost=59,047.23..457,843.59 rows=271,304 width=512) (actual time=1,143.425..8,016.596 rows=211,092 loops=4)

  • Output: ont.codigo_entidade, id_ont.manager_code, ids_placas.codigo_entidade, ids_placas.id_externo, placas.estado_administrativo, ids_placas.tipo_entidade, ids_placas.obje
  • Inner Unique: true
  • Hash Cond: (oe.profile_id = co.id)
  • Buffers: shared hit=9467498
  • Worker 0: actual time=948.591..8030.307 rows=228676 loops=1
  • Buffers: shared hit=2533876
  • Worker 1: actual time=1498.487..8259.711 rows=214032 loops=1
  • Buffers: shared hit=2391773
  • Worker 2: actual time=949.220..8030.852 rows=231085 loops=1
  • Buffers: shared hit=2556907
3. 92.610 7,831.098 ↑ 1.3 211,092 4

Hash Join (cost=59,045.28..453,795.51 rows=271,304 width=381) (actual time=1,143.158..7,831.098 rows=211,092 loops=4)

  • Output: id_ont.manager_code, id_ont.object_id, ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_serie,
  • Inner Unique: true
  • Hash Cond: (ids_portos.codigo_placa = placas.codigo_entidade)
  • Buffers: shared hit=9467371
  • Worker 0: actual time=948.288..7835.871 rows=228676 loops=1
  • Buffers: shared hit=2533834
  • Worker 1: actual time=1498.093..8077.591 rows=214032 loops=1
  • Buffers: shared hit=2391731
  • Worker 2: actual time=948.909..7836.575 rows=231085 loops=1
  • Buffers: shared hit=2556865
4. 111.336 7,251.068 ↑ 1.3 211,092 4

Hash Join (cost=32,774.34..424,099.15 rows=271,320 width=387) (actual time=651.279..7,251.068 rows=211,092 loops=4)

  • Output: id_ont.manager_code, id_ont.object_id, ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_
  • Inner Unique: true
  • Hash Cond: (ids_portos.codigo_placa = ids_placas.codigo_entidade)
  • Buffers: shared hit=9438379
  • Worker 0: actual time=545.647..7342.051 rows=228676 loops=1
  • Buffers: shared hit=2526586
  • Worker 1: actual time=832.792..7328.933 rows=214032 loops=1
  • Buffers: shared hit=2384483
  • Worker 2: actual time=545.752..7341.854 rows=231085 loops=1
  • Buffers: shared hit=2549617
5. 425.317 6,495.924 ↑ 1.3 211,092 4

Merge Join (cost=424.23..388,323.63 rows=271,320 width=312) (actual time=1.751..6,495.924 rows=211,092 loops=4)

  • Output: id_ont.manager_code, id_ont.object_id, ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.n
  • Inner Unique: true
  • Merge Cond: (ont.codigo_entidade = id_ont.codigo_entidade)
  • Buffers: shared hit=9385075
  • Worker 0: actual time=0.449..6688.296 rows=228676 loops=1
  • Buffers: shared hit=2513260
  • Worker 1: actual time=3.789..6398.182 rows=214032 loops=1
  • Buffers: shared hit=2371157
  • Worker 2: actual time=1.264..6688.384 rows=231085 loops=1
  • Buffers: shared hit=2536291
  • -> Index Scan using ids_elementos_geridos_pkey on agorangmanager.ids_elementos_geridos id_ont (cost=0.42..24917.27 rows=844422 width=36) (actual time=0.
6. 617.360 6,070.607 ↑ 1.1 211,092 4

Nested Loop (cost=346.76..359,017.83 rows=231,838 width=292) (actual time=1.390..6,070.607 rows=211,092 loops=4)

  • Output: ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_serie, ont.nome, ont.locali
  • Inner Unique: true
  • Buffers: shared hit=9203668
  • Worker 0: actual time=0.409..6268.100 rows=228676 loops=1
  • Buffers: shared hit=2467909
  • Worker 1: actual time=2.960..5973.954 rows=214032 loops=1
  • Buffers: shared hit=2325802
  • Worker 2: actual time=1.078..6269.864 rows=231085 loops=1
  • Buffers: shared hit=2490943
  • Output: id_ont.codigo_entidade, id_ont.tipo_entidade, id_ont.object_id, id_ont.id_externo, id_ont.manager_code
  • Filter: (id_ont.tipo_entidade = 10023)
  • Rows Removed by Filter: 55
  • Buffers: shared hit=181407
  • Worker 0: actual time=0.037..251.199 rows=844242 loops=1
  • Buffers: shared hit=45351
  • Worker 1: actual time=0.051..259.996 rows=844370 loops=1
  • Buffers: shared hit=45355
  • Worker 2: actual time=0.038..249.639 rows=844146 loops=1
  • Buffers: shared hit=45348
7. 4,371.438 5,453.245 ↑ 1.1 211,092 4

Nested Loop (cost=346.33..253,362.88 rows=231,905 width=281) (actual time=1.349..5,453.245 rows=211,092 loops=4)

  • Output: ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_serie, ont.nome, ont.
  • Inner Unique: true
  • Buffers: shared hit=5823062
  • Worker 0: actual time=0.365..5621.276 rows=228676 loops=1
  • Buffers: shared hit=1552605
  • Worker 1: actual time=2.905..5364.281 rows=214032 loops=1
  • Buffers: shared hit=1468436
  • Worker 2: actual time=1.031..5621.881 rows=231085 loops=1
  • Buffers: shared hit=1566188
  • -> Index Scan using ids_portos_object_id_key on agorangmanager.ids_portos (cost=0.56..0.72 rows=1 width=87) (actual time=0.015..0.015 rows=1
8. 468.731 1,081.807 ↑ 1.1 211,092 4

Merge Join (cost=345.77..86,775.90 rows=231,905 width=194) (actual time=1.132..1,081.807 rows=211,092 loops=4)

  • Output: ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_serie, ont.nome
  • Inner Unique: true
  • Merge Cond: (ont.codigo_entidade = oe.entity_code)
  • Buffers: shared hit=1592632
  • Worker 0: actual time=0.122..1098.934 rows=228676 loops=1
  • Buffers: shared hit=407578
  • Worker 1: actual time=2.587..1099.515 rows=214032 loops=1
  • Buffers: shared hit=395499
  • Worker 2: actual time=0.803..1093.548 rows=231085 loops=1
  • Buffers: shared hit=408546
  • -> Index Scan using "ONT_EXTENSION_UK_1" on agorangmanager.ont_extension oe (cost=0.42..24217.91 rows=841380 width=59) (actual time=0.
  • Output: ids_portos.codigo_entidade, ids_portos.tipo_entidade, ids_portos.object_id, ids_portos.codigo_placa, ids_portos.id_externo
  • Index Cond: ((ids_portos.object_id)::text = "substring"((dei.caminho)::text, '\/PTIN\d+\/\d+\/\d+$'::text))
  • Buffers: shared hit=4230430
  • Worker 0: actual time=0.015..0.015 rows=1 loops=228676
  • Buffers: shared hit=1145027
  • Worker 1: actual time=0.015..0.015 rows=1 loops=214032
  • Buffers: shared hit=1072937
  • Worker 2: actual time=0.015..0.015 rows=1 loops=231085
  • Buffers: shared hit=1157642
9. 613.076 613.076 ↑ 1.3 211,092 4

Merge Join (cost=129.47..59,760.97 rows=272,397 width=135) (actual time=0.331..613.076 rows=211,092 loops=4)

  • Output: ont.codigo_entidade, ont.estado_administrativo, ont.versao, ont.versao_firmware, ont.data_instalacao, ont.numero_serie, on
  • Inner Unique: true
  • Merge Cond: (ont.codigo_entidade = dei.codigo_entidade)
  • Buffers: shared hit=796716
  • Worker 0: actual time=0.076..632.138 rows=228676 loops=1
  • Buffers: shared hit=208601
  • Worker 1: actual time=0.715..628.918 rows=214032 loops=1
  • Buffers: shared hit=196508
  • Worker 2: actual time=0.181..629.762 rows=231085 loops=1
  • Buffers: shared hit=209579
  • -> Parallel Index Scan using elementos_geridos_pkey on agorangmanager.elementos_geridos ont (cost=0.42..35014.61 rows=318804 wid
  • Output: ont.codigo_entidade, ont.id_dominio_gerido, ont.id_area_central, ont.id_modelo_equipamento, ont.nome, ont.data_insta
  • Buffers: shared hit=352682
  • Worker 0: actual time=0.029..215.507 rows=228689 loops=1
  • Buffers: shared hit=97597
  • Worker 1: actual time=0.034..206.180 rows=214048 loops=1
  • Buffers: shared hit=85493
  • Worker 2: actual time=0.028..215.644 rows=231092 loops=1
  • Buffers: shared hit=98577
  • -> Index Scan using dados_equip_inserido_pkey on agorangmanager.dados_equip_inserido dei (cost=0.42..19357.96 rows=844432 width=
  • Output: dei.codigo_entidade, dei.paric_index, dei.end_modem, dei.end_multiponto, dei.caminho
  • Buffers: shared hit=444034
  • Worker 0: actual time=0.033..224.842 rows=844242 loops=1
  • Buffers: shared hit=111004
  • Worker 1: actual time=0.042..232.632 rows=844370 loops=1
  • Buffers: shared hit=111015
  • Worker 2: actual time=0.032..223.177 rows=844146 loops=1
  • Buffers: shared hit=111002
  • Output: oe.entity_code, oe.description, oe.onu_id, oe.sw_upgrade_mode, oe.sw_version_planned, oe.profile_id, oe.tod_sync, oe.eth_e
  • Buffers: shared hit=795916
  • Worker 0: actual time=0.043..281.789 rows=844242 loops=1
  • Buffers: shared hit=198977
  • Worker 1: actual time=0.051..290.814 rows=844370 loops=1
  • Buffers: shared hit=198991
  • Worker 2: actual time=0.038..279.027 rows=844146 loops=1
  • Buffers: shared hit=198967
10. 0.002 0.002 ↑ 1.0 1 844,370

Index Scan using portos_pkey on agorangmanager.portos (cost=0.43..0.46 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=844,370)

  • Output: portos.codigo_entidade, portos.nome_porto, portos.estado_administrativo, portos.id_dominio_sca, portos.descricao
  • Index Cond: (portos.codigo_entidade = ids_portos.codigo_entidade)
  • Buffers: shared hit=3380606
  • Worker 0: actual time=0.002..0.002 rows=1 loops=228676
  • Buffers: shared hit=915304
  • Worker 1: actual time=0.002..0.002 rows=1 loops=214032
  • Buffers: shared hit=857366
  • Worker 2: actual time=0.002..0.002 rows=1 loops=231085
  • Buffers: shared hit=924755
11. 373.350 643.808 ↑ 1.0 845,443 4

Hash (cost=21,781.16..21,781.16 rows=845,516 width=75) (actual time=643.808..643.808 rows=845,443 loops=4)

  • Output: ids_placas.codigo_entidade, ids_placas.id_externo, ids_placas.tipo_entidade, ids_placas.object_id
  • Buckets: 1048576 Batches: 1 Memory Usage: 98401kB
  • Buffers: shared hit=53304
  • Worker 0: actual time=540.555..540.555 rows=845443 loops=1
  • Buffers: shared hit=13326
  • Worker 1: actual time=822.411..822.411 rows=845443 loops=1
  • Buffers: shared hit=13326
  • Worker 2: actual time=539.180..539.180 rows=845443 loops=1
  • Buffers: shared hit=13326
12. 270.458 270.458 ↑ 1.0 845,443 4

Seq Scan on agorangmanager.ids_placas (cost=0.00..21,781.16 rows=845,516 width=75) (actual time=0.044..270.458 rows=845,443 loops=4)

  • Output: ids_placas.codigo_entidade, ids_placas.id_externo, ids_placas.tipo_entidade, ids_placas.object_id
  • Buffers: shared hit=53304
  • Worker 0: actual time=0.059..232.987 rows=845443 loops=1
  • Buffers: shared hit=13326
  • Worker 1: actual time=0.061..359.566 rows=845443 loops=1
  • Buffers: shared hit=13326
  • Worker 2: actual time=0.041..229.662 rows=845443 loops=1
  • Buffers: shared hit=13326
13. 277.979 487.420 ↑ 1.0 845,442 4

Hash (cost=15,702.64..15,702.64 rows=845,464 width=10) (actual time=487.420..487.420 rows=845,442 loops=4)

  • Output: placas.estado_administrativo, placas.codigo_entidade
  • Buckets: 1048576 Batches: 1 Memory Usage: 47823kB
  • Buffers: shared hit=28992
  • Worker 0: actual time=398.042..398.042 rows=845442 loops=1
  • Buffers: shared hit=7248
  • Worker 1: actual time=658.602..658.602 rows=845442 loops=1
  • Buffers: shared hit=7248
  • Worker 2: actual time=397.743..397.743 rows=845442 loops=1
  • Buffers: shared hit=7248
14. 209.441 209.441 ↑ 1.0 845,442 4

Seq Scan on agorangmanager.placas (cost=0.00..15,702.64 rows=845,464 width=10) (actual time=0.034..209.441 rows=845,442 loops=4)

  • Output: placas.estado_administrativo, placas.codigo_entidade
  • Buffers: shared hit=28992
  • Worker 0: actual time=0.041..166.653 rows=845442 loops=1
  • Buffers: shared hit=7248
  • Worker 1: actual time=0.045..289.290 rows=845442 loops=1
  • Buffers: shared hit=7248
  • Worker 2: actual time=0.033..161.026 rows=845442 loops=1
  • Buffers: shared hit=7248
15. 0.016 0.050 ↑ 1.0 42 4

Hash (cost=1.42..1.42 rows=42 width=154) (actual time=0.050..0.050 rows=42 loops=4)

  • Output: co.profile_name, co.id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=4
  • Worker 0: actual time=0.048..0.048 rows=42 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.066..0.066 rows=42 loops=1
  • Buffers: shared hit=1
  • Worker 2: actual time=0.050..0.050 rows=42 loops=1
  • Buffers: shared hit=1
16. 0.034 0.034 ↑ 1.0 42 4

Seq Scan on agorangmanager.catalogo_perfis_ont co (cost=0.00..1.42 rows=42 width=154) (actual time=0.027..0.034 rows=42 loops=4)

  • Output: co.profile_name, co.id
  • Buffers: shared hit=4
  • Worker 0: actual time=0.030..0.034 rows=42 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.035..0.044 rows=42 loops=1
  • Buffers: shared hit=1
  • Worker 2: actual time=0.032..0.037 rows=42 loops=1
  • Buffers: shared hit=1