explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XaBA

Settings
# exclusive inclusive rows x rows loops node
1. 2.023 770.668 ↓ 5,787.5 11,575 1

Unique (cost=6,330.29..6,330.31 rows=2 width=84) (actual time=768.235..770.668 rows=11,575 loops=1)

2.          

CTE unified_assembly_relations

3. 0.527 748.758 ↓ 5,986.5 11,973 1

Append (cost=6,193.61..6,330.24 rows=2 width=21) (actual time=744.853..748.758 rows=11,973 loops=1)

4. 1.161 746.495 ↓ 11,973.0 11,973 1

Subquery Scan on "*SELECT* 1" (cost=6,193.61..6,193.63 rows=1 width=21) (actual time=744.852..746.495 rows=11,973 loops=1)

5. 19.955 745.334 ↓ 11,973.0 11,973 1

Sort (cost=6,193.61..6,193.62 rows=1 width=25) (actual time=744.849..745.334 rows=11,973 loops=1)

  • Sort Key: rel_1.kogr, rel_1.typnr, rel_1.fixzus
  • Sort Method: quicksort Memory: 1,320kB
6. 3.090 725.379 ↓ 11,973.0 11,973 1

Nested Loop (cost=0.56..6,193.60 rows=1 width=25) (actual time=0.644..725.379 rows=11,973 loops=1)

7. 0.289 0.289 ↓ 1.4 1,000 1

Seq Scan on temp_read_assembly_parents_input indata (cost=0.00..17.20 rows=720 width=84) (actual time=0.015..0.289 rows=1,000 loops=1)

8. 722.000 722.000 ↓ 12.0 12 1,000

Index Scan using assembly_relation_kogr_typnr_child_snr_idx on assembly_relation rel_1 (cost=0.56..8.57 rows=1 width=36) (actual time=0.657..0.722 rows=12 loops=1,000)

  • Index Cond: ((kogr = indata.kogr) AND (typnr = indata.typnr) AND (child_snr = indata.ugsnr))
9. 0.001 1.736 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 2" (cost=136.59..136.60 rows=1 width=21) (actual time=1.736..1.736 rows=0 loops=1)

10. 0.008 1.735 ↓ 0.0 0 1

Sort (cost=136.59..136.59 rows=1 width=25) (actual time=1.735..1.735 rows=0 loops=1)

  • Sort Key: bt.kogr, bt.typ, bt.""position"
  • Sort Method: quicksort Memory: 25kB
11. 0.001 1.727 ↓ 0.0 0 1

Nested Loop (cost=123.45..136.58 rows=1 width=25) (actual time=1.727..1.727 rows=0 loops=1)

12. 0.152 1.726 ↓ 0.0 0 1

Merge Join (cost=123.02..132.31 rows=1 width=83) (actual time=1.726..1.726 rows=0 loops=1)

  • Merge Cond: ((bt.sachnr = indata_1.ugsnr) AND (bt.kogr = indata_1.kogr) AND (bt.typ = indata_1.typnr))
13. 0.181 0.519 ↑ 1.0 208 1

Sort (cost=71.65..72.17 rows=208 width=39) (actual time=0.512..0.519 rows=208 loops=1)

  • Sort Key: bt.sachnr, bt.kogr, bt.typ
  • Sort Method: quicksort Memory: 41kB
14. 0.338 0.338 ↑ 1.0 208 1

Seq Scan on lmt_tlm0bt bt (cost=0.00..63.64 rows=208 width=39) (actual time=0.014..0.338 rows=208 loops=1)

  • Filter: ((relevanz_kz = 'P'::bpchar) AND (eaenr = 'E00000'::bpchar))
  • Rows Removed by Filter: 1,435
15. 0.980 1.055 ↓ 1.3 903 1

Sort (cost=51.37..53.17 rows=720 width=84) (actual time=1.025..1.055 rows=903 loops=1)

  • Sort Key: indata_1.ugsnr, indata_1.kogr, indata_1.typnr
  • Sort Method: quicksort Memory: 103kB
16. 0.075 0.075 ↓ 1.4 1,000 1

Seq Scan on temp_read_assembly_parents_input indata_1 (cost=0.00..17.20 rows=720 width=84) (actual time=0.008..0.075 rows=1,000 loops=1)

17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lmt_tlm0bv_staging_pkey1 on lmt_tlm0bv bv (cost=0.42..4.26 rows=1 width=29) (never executed)

  • Index Cond: ((mo = bt.mo) AND (bauk_snr = bt.bauk_snr) AND (sachnr = bt.sachnr) AND (""position"" = bt.""position""))
  • Heap Fetches: 0
18. 17.026 768.645 ↓ 5,986.5 11,973 1

Sort (cost=0.05..0.06 rows=2 width=84) (actual time=768.234..768.645 rows=11,973 loops=1)

  • Sort Key: rel.parent_snr, rel.kogr, rel.typnr
  • Sort Method: quicksort Memory: 1,320kB
19. 751.619 751.619 ↓ 5,986.5 11,973 1

CTE Scan on unified_assembly_relations rel (cost=0.00..0.04 rows=2 width=84) (actual time=744.857..751.619 rows=11,973 loops=1)

Planning time : 2.101 ms
Execution time : 771.646 ms