explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ytt8

Settings
# exclusive inclusive rows x rows loops node
1. 952.546 23,345.888 ↓ 134.0 26,804 1

HashAggregate (cost=2,487,657.34..2,487,659.34 rows=200 width=44) (actual time=23,341.855..23,345.888 rows=26,804 loops=1)

  • Group Key: rel.parent_snr
2.          

CTE unified_assembly_relations

3. 4,089.502 15,547.019 ↓ 10.0 19,612,685 1

Unique (cost=951.39..2,027,952.55 rows=1,961,256 width=36) (actual time=1.917..15,547.019 rows=19,612,685 loops=1)

4. 1,645.999 11,457.517 ↓ 1.0 19,612,722 1

Merge Append (cost=951.39..1,880,858.34 rows=19,612,561 width=36) (actual time=1.917..11,457.517 rows=19,612,722 loops=1)

  • Sort Key: assembly_relation.kogr, assembly_relation.typnr, assembly_relation.fixzus
5. 9,809.660 9,809.660 ↑ 1.0 19,612,514 1

Index Scan using assembly_relation_kogr_typnr_fixzus_idx on assembly_relation (cost=0.56..1,487,656.29 rows=19,612,560 width=36) (actual time=0.103..9,809.660 rows=19,612,514 loops=1)

6. 0.268 1.858 ↓ 208.0 208 1

Sort (cost=950.81..950.82 rows=1 width=36) (actual time=1.813..1.858 rows=208 loops=1)

  • Sort Key: bt.kogr, bt.typ, bt.""position"
  • Sort Method: quicksort Memory: 41kB
7. 0.157 1.590 ↓ 208.0 208 1

Nested Loop (cost=0.42..950.80 rows=1 width=36) (actual time=0.074..1.590 rows=208 loops=1)

8. 0.393 0.393 ↑ 1.0 208 1

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

  • Filter: ((relevanz_kz = 'P'::bpchar) AND (eaenr = 'E00000'::bpchar))
  • Rows Removed by Filter: 1,435
9. 1.040 1.040 ↑ 1.0 1 208

Index Only Scan using lmt_tlm0bv_staging_pkey1 on lmt_tlm0bv bv (cost=0.42..4.26 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=208)

  • Index Cond: ((mo = bt.mo) AND (bauk_snr = bt.bauk_snr) AND (sachnr = bt.sachnr) AND (""position"" = bt.""position""))
  • Heap Fetches: 0
10. 2,397.293 22,393.342 ↑ 1.6 6,733,370 1

Hash Join (cost=35.42..432,002.06 rows=11,081,096 width=44) (actual time=24.589..22,393.342 rows=6,733,370 loops=1)

  • Hash Cond: (rel.child_snr = indata.part_number)
11. 19,995.835 19,995.835 ↓ 10.0 19,612,685 1

CTE Scan on unified_assembly_relations rel (cost=0.00..39,225.12 rows=1,961,256 width=88) (actual time=1.919..19,995.835 rows=19,612,685 loops=1)

12. 0.136 0.214 ↑ 1.1 1,000 1

Hash (cost=21.30..21.30 rows=1,130 width=44) (actual time=0.213..0.214 rows=1,000 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 58kB
13. 0.078 0.078 ↑ 1.1 1,000 1

Seq Scan on temp_read_assembly_parent_part_numbers_input indata (cost=0.00..21.30 rows=1,130 width=44) (actual time=0.009..0.078 rows=1,000 loops=1)

Planning time : 1.303 ms
Execution time : 23,472.200 ms