explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MSUg : Optimization for: plan #ppSW

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.027 254.581 ↑ 1.0 1 1

Nested Loop Left Join (cost=22,198.09..43,602.75 rows=1 width=250) (actual time=254.554..254.581 rows=1 loops=1)

  • Join Filter: (m31sts.status = upper(m.status))
  • Rows Removed by Join Filter: 29
  • -> Seq Scan on status m31sts (cost=0.00..1.30 rows=30 width=10) (actual time=0.001..0.002 rows=30 loops=1)"Total runtime: 254.733 ms
2. 0.023 254.554 ↑ 1.0 1 1

Merge Join (cost=22,198.09..43,601.00 rows=1 width=265) (actual time=254.533..254.554 rows=1 loops=1)

  • Merge Cond: (tg.name = tst.pm_group_code_key)
  • Join Filter: (tst.test_step_key = tsr.test_step_key)
  • Rows Removed by Join Filter: 19
3. 0.022 254.397 ↑ 5.5 20 1

Nested Loop (cost=0.00..81,920.38 rows=111 width=33) (actual time=254.384..254.397 rows=20 loops=1)

  • Join Filter: (tsr.test_group_key = tg.test_group_key)
  • Rows Removed by Join Filter: 160
4. 0.008 0.008 ↑ 1.0 9 1

Index Scan using test_group_name_idx on test_group tg (cost=0.00..12.39 rows=9 width=17) (actual time=0.002..0.008 rows=9 loops=1)

5. 0.019 254.367 ↑ 5.5 20 9

Materialize (cost=0.00..81,893.29 rows=111 width=32) (actual time=0.002..28.263 rows=20 loops=9)

6. 0.006 254.348 ↑ 5.5 20 1

Nested Loop (cost=0.00..81,892.73 rows=111 width=32) (actual time=0.011..254.348 rows=20 loops=1)

7. 254.334 254.334 ↑ 1.0 1 1

Seq Scan on activity act (cost=0.00..81,880.34 rows=1 width=16) (actual time=0.003..254.334 rows=1 loops=1)

  • Filter: (activity_key = 12)
  • Rows Removed by Filter: 2257396
8. 0.008 0.008 ↑ 5.6 20 1

Index Scan using test_step_recipe_key_idx on test_step_recipe tsr (cost=0.00..11.27 rows=112 width=32) (actual time=0.005..0.008 rows=20 loops=1)

  • Index Cond: (recipe_key = act.recipe_key)
9. 0.011 0.134 ↑ 1.0 1 1

Materialize (cost=57.37..57.38 rows=1 width=305) (actual time=0.133..0.134 rows=1 loops=1)

10. 0.060 0.123 ↑ 1.0 1 1

Sort (cost=57.37..57.38 rows=1 width=305) (actual time=0.123..0.123 rows=1 loops=1)

  • Sort Key: m.pm_group_code_key
  • Sort Method: quicksort Memory: 25kB
11. 0.003 0.063 ↑ 1.0 1 1

Nested Loop (cost=0.00..57.36 rows=1 width=305) (actual time=0.062..0.063 rows=1 loops=1)

12. 0.003 0.035 ↑ 2.0 1 1

Nested Loop (cost=0.00..36.40 rows=2 width=315) (actual time=0.034..0.035 rows=1 loops=1)

13. 0.003 0.027 ↑ 2.0 1 1

Append (cost=0.00..19.60 rows=2 width=310) (actual time=0.026..0.027 rows=1 loops=1)

14. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on measurement m (cost=0.00..0.00 rows=1 width=400) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((activity_key = 12) AND (line_id = 41))
15. 0.024 0.024 ↑ 1.0 1 1

Index Scan using measurement_old_line_id_idx on measurement_old m (cost=0.00..19.60 rows=1 width=220) (actual time=0.024..0.024 rows=1 loops=1)

  • Index Cond: (line_id = 41)
  • Filter: (activity_key = 12)
16. 0.005 0.005 ↑ 1.0 1 1

Index Scan using migration_recipe_tmp_pkey on migration_recipe_tmp mrt (cost=0.00..8.39 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (test_type_key = m.test_type_key)
17. 0.025 0.025 ↑ 1.0 1 1

Index Scan using pk_migration_test_step_tmp on migration_test_step_tmp tst (cost=0.00..10.47 rows=1 width=46) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((test_type_key = mrt.recipe_key) AND (sub_pm = m.sub_pm) AND (pm_group_code_key = m.pm_group_code_key))