explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5YoEZ : Optimization for: Optimization for: Optimization for: Optimization for: plan #Bbd1; plan #KAry; plan #BwZ3; plan #PIN6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 142,754.047 1,658,452.281 ↓ 12.6 11,012,435 1

Sort (cost=12,198,264.76..12,200,456.29 rows=876,613 width=204) (actual time=1,613,903.795..1,658,452.281 rows=11,012,435 loops=1)

  • Sort Key: ldai.part_number, ldai.model_code, ldai.plant, ldai.ai DESC, ldai.lfeai DESC
  • Sort Method: external merge Disk: 1479672kB
2. 53,556.457 1,515,698.234 ↓ 12.6 11,012,435 1

Nested Loop Left Join (cost=1.15..12,055,877.62 rows=876,613 width=204) (actual time=69.404..1,515,698.234 rows=11,012,435 loops=1)

3. 38,042.718 1,318,980.122 ↓ 12.6 11,012,435 1

Nested Loop Left Join (cost=0.72..10,349,486.65 rows=876,613 width=193) (actual time=68.044..1,318,980.122 rows=11,012,435 loops=1)

4. 53,347.412 1,236,887.664 ↓ 12.6 11,012,435 1

Nested Loop (cost=0.43..8,825,692.55 rows=876,613 width=182) (actual time=68.019..1,236,887.664 rows=11,012,435 loops=1)

5. 452,103.329 1,007,341.292 ↓ 12.6 11,012,435 1

Nested Loop (cost=0.00..7,102,566.07 rows=876,613 width=134) (actual time=61.150..1,007,341.292 rows=11,012,435 loops=1)

  • Join Filter: (ldai.plant = p.plant_code_psdh)
  • Rows Removed by Join Filter: 353534308
6. 157,550.607 157,550.607 ↓ 12.6 11,046,871 1

Seq Scan on lterm_date_ai ldai (cost=0.00..5,261,667.02 rows=876,613 width=46) (actual time=60.961..157,550.607 rows=11,046,871 loops=1)

  • Filter: ("substring"(md5((part_number)::text), 1, 1) = '0'::text)
  • Rows Removed by Filter: 164260112
7. 397,687.268 397,687.356 ↑ 4.2 33 11,046,871

Materialize (cost=0.00..12.10 rows=140 width=132) (actual time=0.001..0.036 rows=33 loops=11,046,871)

8. 0.088 0.088 ↑ 4.2 33 1

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

9. 176,198.960 176,198.960 ↑ 1.0 1 11,012,435

Index Scan using tzt_vtzate_staging_pkey on tzt_vtzate tt (cost=0.43..1.96 rows=1 width=59) (actual time=0.015..0.016 rows=1 loops=11,012,435)

  • Index Cond: (snr = ldai.part_number)
10. 44,049.740 44,049.740 ↓ 0.0 0 11,012,435

Index Scan using tzt_vtzano_staging_pkey1 on tzt_vtzano ano (cost=0.29..1.73 rows=1 width=22) (actual time=0.004..0.004 rows=0 loops=11,012,435)

  • Index Cond: (ldai.part_number = snr)
11. 143,161.655 143,161.655 ↑ 1.0 1 11,012,435

Index Scan using tzt_vtzapr_staging_pkey on tzt_vtzapr apr (cost=0.43..1.94 rows=1 width=22) (actual time=0.012..0.013 rows=1 loops=11,012,435)

  • Index Cond: (ldai.part_number = snr)
Planning time : 0.345 ms
Execution time : 1,669,856.209 ms