explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g6Bz

Settings
# exclusive inclusive rows x rows loops node
1. 43.175 418,220.855 ↑ 1.0 1 1

Aggregate (cost=8,984.06..8,984.07 rows=1 width=0) (actual time=418,220.855..418,220.855 rows=1 loops=1)

  • Buffers: shared hit=550741 read=147403 dirtied=47
2. 1,418.218 418,177.680 ↓ 28,533.0 28,533 1

Nested Loop (cost=3.05..8,984.06 rows=1 width=0) (actual time=90,539.020..418,177.680 rows=28,533 loops=1)

  • Join Filter: (bei.ent_sta_id = bes.ent_sta_id_auto)
  • Rows Removed by Join Filter: 1826177
  • Buffers: shared hit=550741 read=147403 dirtied=47
3. 124.199 414,448.208 ↓ 28,534.0 28,534 1

Nested Loop (cost=3.05..8,979.60 rows=1 width=8) (actual time=90,538.951..414,448.208 rows=28,534 loops=1)

  • Join Filter: (po.pool_id_auto = up.pool_id)
  • Buffers: shared hit=493673 read=147403 dirtied=47
4. 136.475 413,810.397 ↓ 28,534.0 28,534 1

Nested Loop (cost=2.76..8,979.29 rows=1 width=16) (actual time=90,538.898..413,810.397 rows=28,534 loops=1)

  • Buffers: shared hit=408073 read=147401 dirtied=47
5. 156.410 413,445.650 ↓ 28,534.0 28,534 1

Nested Loop (cost=2.49..8,978.98 rows=1 width=12) (actual time=90,538.849..413,445.650 rows=28,534 loops=1)

  • Join Filter: ((p.pro_id_auto = pei.pro_id) AND (p.pro_ver_id = pei.pro_ver_id) AND (pe.pro_ele_id_auto = pei.pro_ele_id))
  • Buffers: shared hit=351004 read=147401 dirtied=47
6. 111.565 151,273.090 ↓ 29,194.0 29,194 1

Merge Join (cost=1.93..8,974.98 rows=1 width=44) (actual time=90,538.766..151,273.090 rows=29,194 loops=1)

  • Merge Cond: (pe.tsk_id = t.tsk_id_auto)
  • Buffers: shared hit=244403 read=81025 dirtied=9
7. 156.085 151,088.450 ↓ 7,298.8 29,195 1

Nested Loop (cost=1.55..35,855.83 rows=4 width=48) (actual time=90,538.717..151,088.450 rows=29,195 loops=1)

  • Join Filter: ((pe.pro_id = p.pro_id_auto) AND (pe.pro_ver_id = p.pro_ver_id))
  • Buffers: shared hit=244397 read=81025 dirtied=9
8. 38,695.735 150,698.805 ↓ 4,170.7 29,195 1

Nested Loop (cost=1.28..35,853.63 rows=7 width=44) (actual time=90,538.670..150,698.805 rows=29,195 loops=1)

  • Join Filter: ((pi.pro_id = pe.pro_id) AND (pi.pro_ver_id = pe.pro_ver_id))
  • Rows Removed by Join Filter: 74999386
  • Buffers: shared hit=186006 read=81025 dirtied=9
9. 20.594 20.594 ↑ 3.0 2,571 1

Index Scan using ix_pro_element_01 on pro_element pe (cost=0.28..567.08 rows=7,699 width=20) (actual time=0.028..20.594 rows=2,571 loops=1)

  • Index Cond: (env_id = 1001)
  • Buffers: shared hit=2197
10. 32,711.319 111,982.476 ↓ 14,591.5 29,183 2,571

Materialize (cost=0.99..35,017.09 rows=2 width=24) (actual time=0.001..43.556 rows=29,183 loops=2,571)

  • Buffers: shared hit=183809 read=81025 dirtied=9
11. 98.752 79,271.157 ↓ 14,597.0 29,194 1

Nested Loop (cost=0.99..35,017.08 rows=2 width=24) (actual time=0.155..79,271.157 rows=29,194 loops=1)

  • Buffers: shared hit=183809 read=81025 dirtied=9
12. 66,164.297 66,164.297 ↓ 243.9 28,779 1

Index Scan using ix_bus_ent_instance_atts_04 on bus_ent_instance bei (cost=0.56..32,786.96 rows=118 width=12) (actual time=0.113..66,164.297 rows=28,779 loops=1)

  • Index Cond: ((bus_ent_id = 1006) AND ((att_value_4)::text = '10'::text) AND (reg_status = 0) AND (env_id = 1001))
  • Filter: ((att_value_9 IS NULL) AND (att_value_num_5 = '1'::numeric))
  • Rows Removed by Filter: 136286
  • Buffers: shared hit=76726 read=71805 dirtied=8
13. 13,008.108 13,008.108 ↑ 2.0 1 28,779

Index Scan using ix_pro_instance_01 on pro_instance pi (cost=0.43..18.88 rows=2 width=20) (actual time=0.443..0.452 rows=1 loops=28,779)

  • Index Cond: ((bus_ent_inst_id = bei.bus_ent_inst_id_auto) AND (env_id = 1001))
  • Filter: (((pro_inst_status)::text <> 'S'::text) AND ((pro_inst_name_pre)::text = 'DPA'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=107083 read=9220 dirtied=1
14. 233.560 233.560 ↑ 1.0 1 29,195

Index Only Scan using pk_process on process p (cost=0.28..0.30 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=29,195)

  • Index Cond: ((env_id = 1001) AND (pro_id_auto = pi.pro_id) AND (pro_ver_id = pi.pro_ver_id))
  • Heap Fetches: 0
  • Buffers: shared hit=58391
15. 73.075 73.075 ↓ 343.6 29,202 1

Index Only Scan using pk_task on task t (cost=0.14..9.63 rows=85 width=8) (actual time=0.028..73.075 rows=29,202 loops=1)

  • Index Cond: (env_id = 1001)
  • Heap Fetches: 29202
  • Buffers: shared hit=6
16. 262,016.150 262,016.150 ↑ 1.0 1 29,194

Index Scan using pk_pro_ele_instance on pro_ele_instance pei (cost=0.56..3.99 rows=1 width=24) (actual time=6.249..8.975 rows=1 loops=29,194)

  • Index Cond: ((env_id = 1001) AND (pro_inst_id = pi.pro_inst_id_auto))
  • Filter: (((pro_ele_inst_status)::text = 'R'::text) AND (pi.pro_id = pro_id) AND (pi.pro_ver_id = pro_ver_id))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=106601 read=66376 dirtied=38
17. 228.272 228.272 ↑ 1.0 1 28,534

Index Only Scan using pk_pool on pool po (cost=0.28..0.29 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=28,534)

  • Index Cond: (pool_id_auto = pei.pool_id)
  • Heap Fetches: 0
  • Buffers: shared hit=57069
18. 513.612 513.612 ↑ 1.0 1 28,534

Index Scan using pk_usr_pool on usr_pool up (cost=0.28..0.31 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=28,534)

  • Index Cond: (((usr_login)::text = 'uy_ci_45488512'::text) AND (pool_id = pei.pool_id))
  • Filter: (reg_status = 0)
  • Buffers: shared hit=85600 read=2
19. 2,311.254 2,311.254 ↑ 1.0 65 28,534

Seq Scan on bus_ent_status bes (cost=0.00..3.64 rows=65 width=8) (actual time=0.007..0.081 rows=65 loops=28,534)

  • Filter: ((env_id = 1001) AND ((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=57068
Planning time : 41.272 ms
Execution time : 418,223.084 ms