explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vEaL

Settings
# exclusive inclusive rows x rows loops node
1. 3.644 1,243,777.375 ↑ 1.0 1 1

Aggregate (cost=9,499.49..9,499.50 rows=1 width=0) (actual time=1,243,777.375..1,243,777.375 rows=1 loops=1)

  • Buffers: shared hit=25483508 read=148895 dirtied=48
2. 4.169 1,243,773.731 ↓ 24,182.0 24,182 1

Nested Loop (cost=317.12..9,499.48 rows=1 width=0) (actual time=1,243,640.488..1,243,773.731 rows=24,182 loops=1)

  • Buffers: shared hit=25483508 read=148895 dirtied=48
3. 3.367 1,243,721.192 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.98..9,499.10 rows=1 width=8) (actual time=1,243,640.426..1,243,721.192 rows=24,185 loops=1)

  • Join Filter: (up.pool_id = po.pool_id_auto)
  • Buffers: shared hit=25435138 read=148895 dirtied=48
4. 34.310 1,243,669.455 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.70..9,497.71 rows=1 width=16) (actual time=1,243,640.012..1,243,669.455 rows=24,185 loops=1)

  • Join Filter: (pei.pool_id = up.pool_id)
  • Rows Removed by Join Filter: 145110
  • Buffers: shared hit=25362582 read=148895 dirtied=48
5. 0.095 0.095 ↑ 1.0 7 1

Index Scan using pk_usr_pool on usr_pool up (cost=0.28..31.86 rows=7 width=4) (actual time=0.053..0.095 rows=7 loops=1)

  • Index Cond: ((usr_login)::text = 'uy_ci_45488512'::text)
  • Filter: (reg_status = 0)
  • Buffers: shared hit=3
6. 62.587 1,243,635.050 ↓ 24,185.0 24,185 7

Materialize (cost=316.42..9,465.75 rows=1 width=12) (actual time=15,861.888..177,662.150 rows=24,185 loops=7)

  • Buffers: shared hit=25362579 read=148895 dirtied=48
7. 60.730 1,243,572.463 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.42..9,465.74 rows=1 width=12) (actual time=111,033.203..1,243,572.463 rows=24,185 loops=1)

  • Buffers: shared hit=25362579 read=148895 dirtied=48
8. 149,449.548 1,243,342.438 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.14..9,464.29 rows=1 width=44) (actual time=111,032.580..1,243,342.438 rows=24,185 loops=1)

  • Join Filter: (pi.pro_inst_id_auto = pei.pro_inst_id)
  • Rows Removed by Join Filter: 1498275022
  • Buffers: shared hit=25314209 read=148894 dirtied=48
9. 65.988 122,820.678 ↓ 24,629.0 24,629 1

Merge Join (cost=315.58..9,459.79 rows=1 width=40) (actual time=110,984.950..122,820.678 rows=24,629 loops=1)

  • Merge Cond: (pe.tsk_id = t.tsk_id_auto)
  • Buffers: shared hit=93211 read=148894 dirtied=19
10. 9,012.147 122,702.392 ↓ 6,157.5 24,630 1

Nested Loop (cost=315.21..36,854.11 rows=4 width=44) (actual time=110,984.191..122,702.392 rows=24,630 loops=1)

  • Join Filter: ((pi.pro_id = pe.pro_id) AND (pi.pro_ver_id = pe.pro_ver_id))
  • Rows Removed by Join Filter: 76448423
  • Buffers: shared hit=93205 read=148893 dirtied=19
11. 19.963 19.963 ↑ 2.5 3,106 1

Index Scan using ix_pro_element_01 on pro_element pe (cost=0.28..4,369.83 rows=7,699 width=20) (actual time=1.508..19.963 rows=3,106 loops=1)

  • Index Cond: (env_id = 1001)
  • Buffers: shared hit=2203 read=15
12. 4,562.609 113,670.282 ↓ 24,621.0 24,621 3,106

Materialize (cost=314.93..32,349.55 rows=1 width=24) (actual time=0.097..36.597 rows=24,621 loops=3,106)

  • Buffers: shared hit=91002 read=148878 dirtied=19
13. 57.153 109,107.673 ↓ 24,629.0 24,629 1

Nested Loop (cost=314.93..32,349.54 rows=1 width=24) (actual time=299.577..109,107.673 rows=24,629 loops=1)

  • Buffers: shared hit=91002 read=148878 dirtied=19
14. 99,730.930 99,995.606 ↓ 239.7 24,211 1

Bitmap Heap Scan on bus_ent_instance bei (cost=314.50..30,484.49 rows=101 width=12) (actual time=299.531..99,995.606 rows=24,211 loops=1)

  • Recheck 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: 134666
  • Heap Blocks: exact=141367
  • Buffers: shared hit=3303 read=138696 dirtied=19
15. 264.676 264.676 ↓ 19.5 158,877 1

Bitmap Index Scan on ix_bus_ent_instance_atts_04 (cost=0.00..314.47 rows=8,127 width=0) (actual time=264.676..264.676 rows=158,877 loops=1)

  • Index Cond: ((bus_ent_id = 1006) AND ((att_value_4)::text = '10'::text) AND (reg_status = 0) AND (env_id = 1001))
  • Buffers: shared read=632
16. 9,054.914 9,054.914 ↑ 1.0 1 24,211

Index Scan using ix_pro_instance_01 on pro_instance pi (cost=0.43..18.46 rows=1 width=20) (actual time=0.370..0.374 rows=1 loops=24,211)

  • 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=87699 read=10182
17. 52.298 52.298 ↓ 289.8 24,637 1

Index Only Scan using pk_task on task t (cost=0.14..9.63 rows=85 width=8) (actual time=0.736..52.298 rows=24,637 loops=1)

  • Index Cond: (env_id = 1001)
  • Heap Fetches: 24637
  • Buffers: shared hit=6 read=1
18. 971,072.212 971,072.212 ↓ 60,835.0 60,835 24,629

Index Only Scan using ix_pro_ele_inst_status on pro_ele_instance pei (cost=0.56..4.49 rows=1 width=24) (actual time=0.024..39.428 rows=60,835 loops=24,629)

  • Index Cond: ((pro_ele_inst_status = 'R'::text) AND (pro_ver_id = pe.pro_ver_id) AND (pro_id = pe.pro_id) AND (pro_ele_id = pe.pro_ele_id_auto) AND (env_id = 1001))
  • Heap Fetches: 1690287
  • Buffers: shared hit=25220998 dirtied=29
19. 169.295 169.295 ↑ 1.0 1 24,185

Index Only Scan using pk_process on process p (cost=0.28..1.45 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=24,185)

  • Index Cond: ((env_id = 1001) AND (pro_id_auto = pe.pro_id) AND (pro_ver_id = pe.pro_ver_id))
  • Heap Fetches: 0
  • Buffers: shared hit=48370 read=1
20. 48.370 48.370 ↑ 1.0 1 24,185

Index Only Scan using pk_pool on pool po (cost=0.28..1.38 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=24,185)

  • Index Cond: (pool_id_auto = pei.pool_id)
  • Heap Fetches: 24185
  • Buffers: shared hit=72556
21. 48.370 48.370 ↑ 1.0 1 24,185

Index Scan using pk_bus_ent_status on bus_ent_status bes (cost=0.14..0.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=24,185)

  • Index Cond: ((env_id = 1001) AND (ent_sta_id_auto = bei.ent_sta_id))
  • Filter: ((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: 0
  • Buffers: shared hit=48370
Planning time : 55.020 ms
Execution time : 1,243,778.701 ms