explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5CKV

Settings
# exclusive inclusive rows x rows loops node
1. 2.796 1,212,829.149 ↑ 1.0 1 1

Aggregate (cost=9,499.48..9,499.49 rows=1 width=0) (actual time=1,212,829.149..1,212,829.149 rows=1 loops=1)

2. 19.835 1,212,826.353 ↓ 24,182.0 24,182 1

Nested Loop (cost=317.12..9,499.48 rows=1 width=0) (actual time=1,212,712.158..1,212,826.353 rows=24,182 loops=1)

3. 19.213 1,212,782.333 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.98..9,499.10 rows=1 width=8) (actual time=1,212,712.088..1,212,782.333 rows=24,185 loops=1)

  • Join Filter: (up.pool_id = po.pool_id_auto)
4. 39.405 1,212,738.935 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.70..9,497.71 rows=1 width=16) (actual time=1,212,711.982..1,212,738.935 rows=24,185 loops=1)

  • Join Filter: (pei.pool_id = up.pool_id)
  • Rows Removed by Join Filter: 145110
5. 1.057 1.057 ↑ 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=1.010..1.057 rows=7 loops=1)

  • Index Cond: ((usr_login)::text = 'uy_ci_45488512'::text)
  • Filter: (reg_status = 0)
6. 65.219 1,212,698.473 ↓ 24,185.0 24,185 7

Materialize (cost=316.42..9,465.75 rows=1 width=12) (actual time=14,897.915..173,242.639 rows=24,185 loops=7)

7. 74.148 1,212,633.254 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.42..9,465.74 rows=1 width=12) (actual time=104,285.398..1,212,633.254 rows=24,185 loops=1)

8. 133,065.634 1,212,389.811 ↓ 24,185.0 24,185 1

Nested Loop (cost=316.14..9,464.28 rows=1 width=44) (actual time=104,285.378..1,212,389.811 rows=24,185 loops=1)

  • Join Filter: (pi.pro_inst_id_auto = pei.pro_inst_id)
  • Rows Removed by Join Filter: 1498275022
9. 65.643 115,616.036 ↓ 24,629.0 24,629 1

Merge Join (cost=315.58..9,459.79 rows=1 width=40) (actual time=104,240.935..115,616.036 rows=24,629 loops=1)

  • Merge Cond: (pe.tsk_id = t.tsk_id_auto)
10. 8,639.927 115,490.500 ↓ 6,157.5 24,630 1

Nested Loop (cost=315.21..36,854.11 rows=4 width=44) (actual time=104,240.066..115,490.500 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
11. 25.915 25.915 ↑ 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.530..25.915 rows=3,106 loops=1)

  • Index Cond: (env_id = 1001)
12. 4,347.690 106,824.658 ↓ 24,621.0 24,621 3,106

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

13. 62.808 102,476.968 ↓ 24,629.0 24,629 1

Nested Loop (cost=314.93..32,349.54 rows=1 width=24) (actual time=442.953..102,476.968 rows=24,629 loops=1)

14. 91,637.289 92,027.641 ↓ 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=442.894..92,027.641 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
15. 390.352 390.352 ↓ 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=390.352..390.352 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))
16. 10,386.519 10,386.519 ↑ 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.425..0.429 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
17. 59.893 59.893 ↓ 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.852..59.893 rows=24,637 loops=1)

  • Index Cond: (env_id = 1001)
  • Heap Fetches: 24637
18. 963,708.141 963,708.141 ↓ 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.025..39.129 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
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
20. 24.185 24.185 ↑ 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.001 rows=1 loops=24,185)

  • Index Cond: (pool_id_auto = pei.pool_id)
  • Heap Fetches: 24185
21. 24.185 24.185 ↑ 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.001..0.001 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
Planning time : 33.165 ms