explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dNjX

Settings
# exclusive inclusive rows x rows loops node
1. 1,445.322 1,445.322 ↑ 43.8 156,632 1

CTE Scan on cte_recursive_sk (cost=842,178.26..979,354.44 rows=6,858,809 width=1,411) (actual time=0.067..1,445.322 rows=156,632 loops=1)

  • Output: cte_recursive_sk.parent_id, cte_recursive_sk.parent_lid, cte_recursive_sk.tu_id, cte_recursive_sk.tu_lid, cte_recursive_sk.tu_type, cte_recursive_sk.status, cte_recursive_sk.dummy, cte_recursive_sk.upperparent_id, cte_recursive_sk.parent_type, cte_recursive_sk.parent_level, cte_recursive_sk.gendate
2.          

CTE cte_recursive_sk

3. 103.545 1,264.227 ↑ 43.8 156,632 1

Recursive Union (cost=0.55..842,178.26 rows=6,858,809 width=1,347) (actual time=0.064..1,264.227 rows=156,632 loops=1)

4. 588.958 588.958 ↓ 1.0 68,305 1

Index Scan using i_stockkeepable_stocktypeid on inther_lc.wms_stockkeepable sk (cost=0.55..32,539.31 rows=67,909 width=186) (actual time=0.062..588.958 rows=68,305 loops=1)

  • Output: NULL::text, NULL::text, sk.id, sk.logical_id, sk.stockkeepabletype_id, sk.status, sk.dummy, sk.parent_id, NULL::text, 0, sk.gendate
  • Index Cond: ((sk.stockkeepabletype_id)::text = ANY ('{TT_CARTON_3,TT_CARTON_3B,TT_ORDERTOTE,TT_RF_TU,TT_STOCKTOTE_MINILOAD,TT_STOCKTOTE_SHUTTLE,TT_STOCKTOTE_P04,TT_STOCKPARTITION_P04}'::text[]))
  • Filter: (NOT sk.dummy)
  • Rows Removed by Filter: 1524
5. 124.527 571.724 ↑ 30.8 22,082 4

Hash Join (cost=42,629.26..67,246.28 rows=679,090 width=1,347) (actual time=103.074..142.931 rows=22,082 loops=4)

  • Output: COALESCE(rsk.parent_id, (sk_1.id)::text), COALESCE(rsk.parent_lid, (sk_1.logical_id)::text), rsk.tu_id, rsk.tu_lid, rsk.tu_type, rsk.status, rsk.dummy, sk_1.parent_id, COALESCE(rsk.parent_type, (sk_1.stockkeepabletype_id)::text), (rsk.parent_level + 1), rsk.gendate
  • Hash Cond: ((rsk.upperparent_id)::text = (sk_1.id)::text)
6. 37.684 37.684 ↑ 17.3 39,158 4

WorkTable Scan on cte_recursive_sk rsk (cost=0.00..13,581.80 rows=679,090 width=1,411) (actual time=0.001..9.421 rows=39,158 loops=4)

  • Output: rsk.parent_id, rsk.parent_lid, rsk.tu_id, rsk.tu_lid, rsk.tu_type, rsk.status, rsk.dummy, rsk.upperparent_id, rsk.parent_type, rsk.parent_level, rsk.gendate
7. 171.381 409.513 ↑ 1.0 364,119 1

Hash (cost=38,076.34..38,076.34 rows=364,234 width=72) (actual time=409.513..409.513 rows=364,119 loops=1)

  • Output: sk_1.id, sk_1.logical_id, sk_1.parent_id, sk_1.stockkeepabletype_id
  • Buckets: 524288 Batches: 1 Memory Usage: 34288kB
8. 238.132 238.132 ↑ 1.0 364,119 1

Seq Scan on inther_lc.wms_stockkeepable sk_1 (cost=0.00..38,076.34 rows=364,234 width=72) (actual time=0.016..238.132 rows=364,119 loops=1)

  • Output: sk_1.id, sk_1.logical_id, sk_1.parent_id, sk_1.stockkeepabletype_id