explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KAry : Optimization for: plan #Bbd1

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 28,864.024 600,747.372 ↓ 12.6 11,012,435 1

Unique (cost=6,030,733.85..6,072,371.97 rows=876,592 width=204) (actual time=558,520.209..600,747.372 rows=11,012,435 loops=1)

2. 129,387.014 571,883.348 ↓ 12.6 11,012,435 1

Sort (cost=6,030,733.85..6,032,925.33 rows=876,592 width=204) (actual time=558,520.207..571,883.348 rows=11,012,435 loops=1)

  • Sort Key: ldai.part_number, ldai.model_code, ldai.plant, ldai.ai DESC, ldai.lfeai DESC, p.plant_code_sap, p.plant_code_mstais, ldai.nrcl, tt.tbenn, ldai.launch_date, ldai.launch_package, ldai.pseudo, tt.ba, tt.ta, tt.ch, (CASE WHEN (ano.noekogr IS (...)
  • Sort Method: external sort Disk: 1479848kB
3. 26,842.415 442,496.334 ↓ 12.6 11,012,435 1

Hash Join (cost=5,349,928.48..5,888,348.94 rows=876,592 width=204) (actual time=228,611.264..442,496.334 rows=11,012,435 loops=1)

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
4. 47,219.577 415,653.824 ↓ 12.6 11,046,871 1

Merge Left Join (cost=5,349,915.33..5,876,282.65 rows=876,592 width=116) (actual time=228,611.145..415,653.824 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = apr.snr)
5. 47,108.372 348,169.054 ↓ 12.6 11,046,871 1

Merge Left Join (cost=5,349,867.86..5,649,090.59 rows=876,592 width=105) (actual time=228,592.392..348,169.054 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = tt.snr)
6. 26,360.732 280,210.298 ↓ 12.6 11,046,871 1

Merge Left Join (cost=5,349,817.82..5,356,626.95 rows=876,592 width=57) (actual time=228,568.340..280,210.298 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = ano.snr)
7. 107,958.836 253,676.373 ↓ 12.6 11,046,871 1

Sort (cost=5,348,070.53..5,350,262.01 rows=876,592 width=46) (actual time=228,456.814..253,676.373 rows=11,046,871 loops=1)

  • Sort Key: ldai.part_number
  • Sort Method: external merge Disk: 627984kB
8. 145,717.537 145,717.537 ↓ 12.6 11,046,871 1

Seq Scan on lterm_date_ai ldai (cost=0.00..5,261,544.12 rows=876,592 width=46) (actual time=57,741.263..145,717.537 rows=11,046,871 loops=1)

  • Filter: ("substring"(md5((part_number)::text), 1, 1) = '0'::text)
  • Rows Removed by Filter: 164260112
9. 144.713 173.193 ↓ 3.1 58,329 1

Sort (cost=1,747.29..1,793.63 rows=18,535 width=22) (actual time=111.522..173.193 rows=58,329 loops=1)

  • Sort Key: ano.snr
  • Sort Method: quicksort Memory: 2217kB
10. 28.480 28.480 ↑ 1.0 18,535 1

Seq Scan on tzt_vtzano ano (cost=0.00..433.35 rows=18,535 width=22) (actual time=0.013..28.480 rows=18,535 loops=1)

11. 20,850.384 20,850.384 ↓ 3.1 16,093,209 1

Index Scan using tzt_vtzate_staging_pkey on tzt_vtzate tt (cost=0.43..268,919.25 rows=5,110,925 width=59) (actual time=3.335..20,850.384 rows=16,093,209 loops=1)

12. 20,265.193 20,265.193 ↓ 3.2 15,990,235 1

Index Scan using tzt_vtzapr_staging_pkey on tzt_vtzapr apr (cost=0.43..203,606.01 rows=5,051,460 width=22) (actual time=0.009..20,265.193 rows=15,990,235 loops=1)

13. 0.045 0.095 ↑ 4.2 33 1

Hash (cost=11.40..11.40 rows=140 width=132) (actual time=0.095..0.095 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.050 0.050 ↑ 4.2 33 1

Seq Scan on master_data p (cost=0.00..11.40 rows=140 width=132) (actual time=0.013..0.050 rows=33 loops=1)

Planning time : 9.955 ms
Execution time : 612,585.194 ms