explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NStd

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 7,168.032 ↑ 1.0 1 1

Aggregate (cost=9,907.28..9,907.29 rows=1 width=0) (actual time=7,168.031..7,168.032 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=4,597,501
2. 0.023 7,168.019 ↑ 1.0 1 1

Nested Loop (cost=365.84..9,907.28 rows=1 width=0) (actual time=6,840.065..7,168.019 rows=1 loops=1)

  • Join Filter: (bei.ent_sta_id = bes.ent_sta_id_auto)
  • Rows Removed by Join Filter: 64
  • Buffers: shared hit=4,597,501
3. 410.863 7,167.935 ↑ 1.0 1 1

Nested Loop (cost=365.84..9,902.82 rows=1 width=8) (actual time=6,840.008..7,167.935 rows=1 loops=1)

  • Output: bei.env_id, bei.ent_sta_id
  • Buffers: shared hit=4,597,499
4. 230.429 3,890.697 ↓ 573,275.0 573,275 1

Nested Loop (cost=365.42..9,899.51 rows=1 width=8) (actual time=13.663..3,890.697 rows=573,275 loops=1)

  • Output: pi.env_id, pi.bus_ent_inst_id
  • Join Filter: ((p.pro_id_auto = pi.pro_id) AND (p.pro_ver_id = pi.pro_ver_id))
  • Buffers: shared hit=2,381,817
5. 288.076 1,316.720 ↓ 585,887.0 585,887 1

Nested Loop (cost=364.99..9,898.76 rows=1 width=32) (actual time=13.602..1,316.720 rows=585,887 loops=1)

  • Output: pei.pro_id, pei.pro_ver_id, pei.pro_inst_id, p.env_id, p.pro_id_auto, p.pro_ver_id, pe.pro_id, pe.pro_ver_id
  • Join Filter: (po.pool_id_auto = pei.pool_id)
  • Rows Removed by Join Filter: 1,182,820
  • Buffers: shared hit=35,077
6. 0.017 0.199 ↑ 2.0 3 1

Nested Loop (cost=0.56..57.70 rows=6 width=8) (actual time=0.148..0.199 rows=3 loops=1)

  • Output: po.pool_id_auto, up.pool_id
  • Buffers: shared hit=10
7. 0.113 0.113 ↑ 2.0 3 1

Index Scan using pk_usr_pool on public.usr_pool up (cost=0.28..31.88 rows=6 width=4) (actual time=0.097..0.113 rows=3 loops=1)

  • Output: up.usr_login, up.pool_id, up.usr_pool_flags, up.reg_user, up.reg_status, up.reg_date
  • Index Cond: ((up.usr_login)::text = 'uy_ci_43383342'::text)
  • Filter: (up.reg_status = 0)
  • Buffers: shared hit=3
8. 0.069 0.069 ↑ 1.0 1 3

Index Only Scan using pk_pool on public.pool po (cost=0.28..4.29 rows=1 width=4) (actual time=0.019..0.023 rows=1 loops=3)

  • Output: po.pool_id_auto
  • Index Cond: (po.pool_id_auto = up.pool_id)
  • Heap Fetches: 0
  • Buffers: shared hit=7
9. 302.597 1,028.445 ↓ 697.7 589,569 3

Materialize (cost=364.43..9,767.12 rows=845 width=36) (actual time=4.484..342.815 rows=589,569 loops=3)

  • Output: pei.pro_id, pei.pro_ver_id, pei.pro_inst_id, pei.pool_id, p.env_id, p.pro_id_auto, p.pro_ver_id, pe.pro_id, pe.pro_ver_id
  • Buffers: shared hit=35,067
10. 169.539 725.848 ↓ 697.7 589,569 1

Nested Loop (cost=364.43..9,762.89 rows=845 width=36) (actual time=13.436..725.848 rows=589,569 loops=1)

  • Output: pei.pro_id, pei.pro_ver_id, pei.pro_inst_id, pei.pool_id, p.env_id, p.pro_id_auto, p.pro_ver_id, pe.pro_id, pe.pro_ver_id
  • Join Filter: ((p.pro_id_auto = pei.pro_id) AND (p.pro_ver_id = pei.pro_ver_id))
  • Buffers: shared hit=35,067
11. 1.297 6.461 ↓ 1.2 2,488 1

Merge Join (cost=363.87..500.82 rows=2,040 width=28) (actual time=4.239..6.461 rows=2,488 loops=1)

  • Output: p.env_id, p.pro_id_auto, p.pro_ver_id, pe.env_id, pe.pro_id, pe.pro_ver_id, pe.pro_ele_id_auto
  • Merge Cond: ((p.pro_id_auto = pe.pro_id) AND (p.pro_ver_id = pe.pro_ver_id))
  • Buffers: shared hit=2,219
12. 0.447 0.447 ↑ 1.0 1,516 1

Index Only Scan using pk_process on public.process p (cost=0.28..90.81 rows=1,516 width=12) (actual time=0.030..0.447 rows=1,516 loops=1)

  • Output: p.env_id, p.pro_id_auto, p.pro_ver_id
  • Index Cond: (p.env_id = 1,001)
  • Heap Fetches: 0
  • Buffers: shared hit=16
13. 1.634 4.717 ↓ 1.0 2,488 1

Sort (cost=363.46..369.65 rows=2,476 width=16) (actual time=4.206..4.717 rows=2,488 loops=1)

  • Output: pe.env_id, pe.pro_id, pe.pro_ver_id, pe.pro_ele_id_auto
  • Sort Key: pe.pro_id, pe.pro_ver_id
  • Sort Method: quicksort Memory: 213kB
  • Buffers: shared hit=2,203
14. 0.875 3.083 ↓ 1.0 2,488 1

Merge Join (cost=0.65..223.89 rows=2,476 width=16) (actual time=0.076..3.083 rows=2,488 loops=1)

  • Output: pe.env_id, pe.pro_id, pe.pro_ver_id, pe.pro_ele_id_auto
  • Merge Cond: (t.tsk_id_auto = pe.tsk_id)
  • Buffers: shared hit=2,199
15. 0.052 0.052 ↑ 1.0 85 1

Index Only Scan using pk_task on public.task t (cost=0.14..9.63 rows=85 width=8) (actual time=0.035..0.052 rows=85 loops=1)

  • Output: t.env_id, t.tsk_id_auto
  • Index Cond: (t.env_id = 1,001)
  • Heap Fetches: 0
  • Buffers: shared hit=2
16. 2.156 2.156 ↑ 3.1 2,489 1

Index Scan using ix_pro_element_01 on public.pro_element pe (cost=0.28..567.45 rows=7,720 width=20) (actual time=0.036..2.156 rows=2,489 loops=1)

  • Output: pe.env_id, pe.pro_id, pe.pro_ver_id, pe.pro_ele_id_auto, pe.pro_ele_start, pe.pro_ele_end, pe.pro_ele_pro_id, pe.pro_ele_iteration, pe.pro_ele_design_xml, pe.pro_ele_multiplier, pe.pro_e (...)
  • Index Cond: (pe.env_id = 1,001)
  • Buffers: shared hit=2,197
17. 549.848 549.848 ↓ 237.0 237 2,488

Index Only Scan using ix_pro_ele_inst_status on public.pro_ele_instance pei (cost=0.56..4.53 rows=1 width=24) (actual time=0.006..0.221 rows=237 loops=2,488)

  • Output: pei.pro_ele_inst_status, pei.pro_ver_id, pei.pro_id, pei.pro_ele_id, pei.pro_inst_id, pei.pool_id, pei.pro_ele_inst_id_auto, pei.pro_ele_inst_date_ready, pei.pro_ele_inst_date_acquired, pei.env_id
  • Index Cond: ((pei.pro_ele_inst_status = 'R'::text) AND (pei.pro_ver_id = pe.pro_ver_id) AND (pei.pro_id = pe.pro_id) AND (pei.pro_ele_id = pe.pro_ele_id_auto) AND (pei.env_id = 1,001))
  • Heap Fetches: 37,756
  • Buffers: shared hit=32,848
18. 2,343.548 2,343.548 ↑ 1.0 1 585,887

Index Scan using pk_pro_instance on public.pro_instance pi (cost=0.43..0.74 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=585,887)

  • Output: pi.env_id, pi.pro_inst_id_auto, pi.pro_inst_name_pre, pi.pro_inst_name_num, pi.pro_inst_name_pos, pi.bus_ent_inst_id, pi.pro_id, pi.pro_ver_id, pi.pro_inst_id_father, pi.pro_inst_ele_id_father, pi.pro_inst_create_user, pi. (...)
  • Index Cond: ((pi.env_id = 1,001) AND (pi.pro_inst_id_auto = pei.pro_inst_id))
  • Filter: (((pi.pro_inst_status)::text <> 'S'::text) AND ((pi.pro_inst_name_pre)::text = 'DPA'::text) AND (pei.pro_id = pi.pro_id) AND (pei.pro_ver_id = pi.pro_ver_id))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,346,740
19. 2,866.375 2,866.375 ↓ 0.0 0 573,275

Index Scan using ix_bus_ent_instance_por_fiscalia on public.bus_ent_instance bei (cost=0.42..3.31 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=573,275)

  • Output: bei.env_id, bei.bus_ent_inst_id_auto, bei.bus_ent_id, bei.bus_ent_inst_name_pos, bei.bus_ent_inst_name_num, bei.bus_ent_inst_name_pre, bei.bus_ent_inst_create_user, bei.bus_ent_inst_create_data, bei.ent_sta_id, bei.att_value_1, (...)
  • Index Cond: ((bei.att_value_9 IS NULL) AND (bei.bus_ent_inst_id_auto = pi.bus_ent_inst_id))
  • Filter: ((bei.att_value_8)::text ~~ '%9860889%'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,215,682
20. 0.061 0.061 ↑ 1.0 65 1

Seq Scan on public.bus_ent_status bes (cost=0.00..3.64 rows=65 width=8) (actual time=0.027..0.061 rows=65 loops=1)

  • Output: bes.env_id, bes.ent_sta_id_auto, bes.ent_sta_name, bes.ent_sta_desc, bes.prj_id, bes.reg_user, bes.reg_status, bes.reg_date
  • Filter: ((bes.env_id = 1,001) AND ((bes.ent_sta_name)::text <> ALL ('{EXTINGUIDO,NC_UNIFICADO,NC_ARCHIVADA,NC_ARCHIVADA_DIFINITIVA,NC_FOTO_SGSP_ALTA,NC_FOTO_SIPPAU,NC_PENDIENTE_PROCESAR,NC_ELIMINADA}'::text[])))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=2
Planning time : 45.507 ms
Execution time : 7,181.028 ms