explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BwZ3 : Optimization for: Optimization for: plan #Bbd1; plan #KAry

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 29,042.382 457,367.606 ↓ 12.6 11,012,435 1

Unique (cost=6,173,002.35..6,214,640.47 rows=876,592 width=204) (actual time=380,114.180..457,367.606 rows=11,012,435 loops=1)

2. 138,797.766 428,325.224 ↓ 12.6 11,012,435 1

Sort (cost=6,173,002.35..6,175,193.83 rows=876,592 width=204) (actual time=380,114.180..428,325.224 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 merge Disk: 1479664kB
3. 26,343.665 289,527.458 ↓ 12.6 11,012,435 1

Hash Left Join (cost=569,386.85..5,999,584.94 rows=876,592 width=204) (actual time=86,833.836..289,527.458 rows=11,012,435 loops=1)

  • Hash Cond: (ldai.part_number = ano.snr)
4. 25,349.094 263,135.515 ↓ 12.6 11,012,435 1

Hash Join (cost=568,721.81..5,993,937.27 rows=876,592 width=193) (actual time=86,785.296..263,135.515 rows=11,012,435 loops=1)

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
5. 32,855.482 237,786.335 ↓ 12.6 11,046,871 1

Hash Left Join (cost=568,708.66..5,981,870.98 rows=876,592 width=105) (actual time=86,785.195..237,786.335 rows=11,046,871 loops=1)

  • Hash Cond: (ldai.part_number = tt.snr)
6. 31,075.107 189,946.022 ↓ 12.6 11,046,871 1

Hash Left Join (cost=240,346.85..5,563,336.07 rows=876,592 width=57) (actual time=71,765.620..189,946.022 rows=11,046,871 loops=1)

  • Hash Cond: (ldai.part_number = apr.snr)
7. 145,072.518 145,072.518 ↓ 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,754.282..145,072.518 rows=11,046,871 loops=1)

  • Filter: ("substring"(md5((part_number)::text), 1, 1) = '0'::text)
  • Rows Removed by Filter: 164260112
8. 6,299.769 13,798.397 ↑ 1.0 5,051,460 1

Hash (cost=147,604.60..147,604.60 rows=5,051,460 width=22) (actual time=13,798.397..13,798.397 rows=5,051,460 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 49705kB
9. 7,498.628 7,498.628 ↑ 1.0 5,051,460 1

Seq Scan on tzt_vtzapr apr (cost=0.00..147,604.60 rows=5,051,460 width=22) (actual time=0.007..7,498.628 rows=5,051,460 loops=1)

10. 6,740.044 14,984.831 ↑ 1.0 5,110,816 1

Hash (cost=209,572.25..209,572.25 rows=5,110,925 width=59) (actual time=14,984.831..14,984.831 rows=5,110,816 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 64959kB
11. 8,244.787 8,244.787 ↑ 1.0 5,110,816 1

Seq Scan on tzt_vtzate tt (cost=0.00..209,572.25 rows=5,110,925 width=59) (actual time=0.007..8,244.787 rows=5,110,816 loops=1)

12. 0.040 0.086 ↑ 4.2 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.046 0.046 ↑ 4.2 33 1

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

14. 23.058 48.278 ↑ 1.0 18,535 1

Hash (cost=433.35..433.35 rows=18,535 width=22) (actual time=48.278..48.278 rows=18,535 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1234kB
15. 25.220 25.220 ↑ 1.0 18,535 1

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

Planning time : 0.992 ms
Execution time : 469,066.536 ms