explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bbd1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 29,029.427 622,058.983 ↓ 12.6 11,012,435 1

Unique (cost=6,030,733.85..6,072,371.97 rows=876,592 width=204) (actual time=542,723.882..622,058.983 rows=11,012,435 loops=1)

2. 149,025.586 593,029.556 ↓ 12.6 11,012,435 1

Sort (cost=6,030,733.85..6,032,925.33 rows=876,592 width=204) (actual time=542,723.880..593,029.556 rows=11,012,435 loops=1)

  • Sort Key: ldai.model_code, ldai.plant, ldai.part_number, 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 merge Disk: 1479656kB
3. 26,869.965 444,003.970 ↓ 12.6 11,012,435 1

Hash Join (cost=5,349,928.48..5,888,348.94 rows=876,592 width=204) (actual time=229,603.470..444,003.970 rows=11,012,435 loops=1)

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
4. 47,166.047 417,133.864 ↓ 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=229,603.306..417,133.864 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = apr.snr)
5. 47,055.176 349,424.349 ↓ 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=229,549.084..349,424.349 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = tt.snr)
6. 26,367.926 281,073.205 ↓ 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=229,530.302..281,073.205 rows=11,046,871 loops=1)

  • Merge Cond: (ldai.part_number = ano.snr)
7. 108,538.670 254,539.706 ↓ 12.6 11,046,871 1

Sort (cost=5,348,070.53..5,350,262.01 rows=876,592 width=46) (actual time=229,425.723..254,539.706 rows=11,046,871 loops=1)

  • Sort Key: ldai.part_number
  • Sort Method: external merge Disk: 627984kB
8. 146,001.036 146,001.036 ↓ 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,551.292..146,001.036 rows=11,046,871 loops=1)

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

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

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

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

11. 21,295.968 21,295.968 ↓ 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=0.455..21,295.968 rows=16,093,209 loops=1)

12. 20,543.468 20,543.468 ↓ 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.010..20,543.468 rows=15,990,235 loops=1)

13. 0.050 0.141 ↑ 4.2 33 1

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

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

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

Planning time : 9.056 ms
Execution time : 633,873.502 ms