explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mvwR : Optimization for: plan #wCXD

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 345,222.815 2,270,402.156 ↓ 3.9 11,212,569 1

Sort (cost=8,565,470.04..8,572,689.72 rows=2,887,873 width=207) (actual time=2,131,396.132..2,270,402.156 rows=11,212,569 loops=1)

  • Sort Key: ldai.part_number, ldai.model_code, ldai.plant, ldai.ai DESC, ldai.lfeai DESC
  • Sort Method: external merge Disk: 1539448kB
2. 354,351.382 1,925,179.341 ↓ 3.9 11,212,569 1

Nested Loop Left Join (cost=572,900.88..8,071,560.01 rows=2,887,873 width=207) (actual time=252,212.000..1,925,179.341 rows=11,212,569 loops=1)

3. 176,038.509 1,223,238.320 ↓ 12.6 11,212,569 1

Hash Left Join (cost=572,900.32..6,089,529.62 rows=890,671 width=204) (actual time=252,208.203..1,223,238.320 rows=11,212,569 loops=1)

  • Hash Cond: (ldai.part_number = ano.snr)
4. 176,214.705 1,046,736.858 ↓ 12.6 11,212,569 1

Hash Join (cost=572,234.74..6,083,813.15 rows=890,671 width=193) (actual time=251,744.771..1,046,736.858 rows=11,212,569 loops=1)

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
5. 190,100.798 870,521.394 ↓ 12.6 11,243,015 1

Hash Join (cost=572,221.59..6,071,553.27 rows=890,671 width=105) (actual time=251,743.959..870,521.394 rows=11,243,015 loops=1)

  • Hash Cond: (ldai.part_number = tt.snr)
6. 188,446.164 581,051.355 ↓ 12.6 11,243,015 1

Hash Left Join (cost=241,834.13..5,650,089.73 rows=890,671 width=57) (actual time=152,340.171..581,051.355 rows=11,243,015 loops=1)

  • Hash Cond: (ldai.part_number = apr.snr)
7. 306,021.579 306,021.579 ↓ 12.6 11,243,015 1

Seq Scan on lterm_date_ai ldai (cost=0.00..5,346,115.52 rows=890,671 width=46) (actual time=65,725.009..306,021.579 rows=11,243,015 loops=1)

  • Filter: ("substring"(md5((part_number)::text), 1, 1) = '0'::text)
  • Rows Removed by Filter: 166903180
8. 41,175.385 86,583.612 ↑ 1.0 5,082,684 1

Hash (cost=148,518.17..148,518.17 rows=5,082,717 width=22) (actual time=86,583.612..86,583.612 rows=5,082,684 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 49909kB
9. 45,408.227 45,408.227 ↑ 1.0 5,082,684 1

Seq Scan on tzt_vtzapr apr (cost=0.00..148,518.17 rows=5,082,717 width=22) (actual time=0.023..45,408.227 rows=5,082,684 loops=1)

10. 46,087.517 99,369.241 ↑ 1.0 5,142,253 1

Hash (cost=210,868.87..210,868.87 rows=5,142,287 width=59) (actual time=99,369.241..99,369.241 rows=5,142,253 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 65305kB
11. 53,281.724 53,281.724 ↑ 1.0 5,142,253 1

Seq Scan on tzt_vtzate tt (cost=0.00..210,868.87 rows=5,142,287 width=59) (actual time=0.020..53,281.724 rows=5,142,253 loops=1)

12. 0.376 0.759 ↑ 4.0 35 1

Hash (cost=11.40..11.40 rows=140 width=132) (actual time=0.759..0.759 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.383 0.383 ↑ 4.0 35 1

Seq Scan on master_data p (cost=0.00..11.40 rows=140 width=132) (actual time=0.038..0.383 rows=35 loops=1)

14. 203.537 462.953 ↑ 1.0 18,559 1

Hash (cost=433.59..433.59 rows=18,559 width=22) (actual time=462.953..462.953 rows=18,559 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1235kB
15. 259.416 259.416 ↑ 1.0 18,559 1

Seq Scan on tzt_vtzano ano (cost=0.00..433.59 rows=18,559 width=22) (actual time=0.020..259.416 rows=18,559 loops=1)

16. 347,589.639 347,589.639 ↑ 1.0 1 11,212,569

Index Scan using tzt_vtzaai_staging_pkey1 on tzt_vtzaai aai (cost=0.56..2.22 rows=1 width=16) (actual time=0.023..0.031 rows=1 loops=11,212,569)

  • Index Cond: ((ldai.part_number = snr) AND (ldai.lfeai = lfeai))
Planning time : 4.402 ms
Execution time : 2,357,220.440 ms