explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 136,488.886 425,811.321 ↓ 12.6 11,012,435 1

Sort (cost=6,173,002.35..6,175,193.83 rows=876,592 width=204) (actual time=378,735.923..425,811.321 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: 1479664kB
2. 26,148.404 289,322.435 ↓ 12.6 11,012,435 1

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

  • Hash Cond: (ldai.part_number = ano.snr)
3. 25,296.252 263,113.290 ↓ 12.6 11,012,435 1

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

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
4. 32,431.969 237,816.947 ↓ 12.6 11,046,871 1

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

  • Hash Cond: (ldai.part_number = tt.snr)
5. 30,718.589 190,237.902 ↓ 12.6 11,046,871 1

Hash Left Join (cost=240,346.85..5,563,336.07 rows=876,592 width=57) (actual time=72,247.147..190,237.902 rows=11,046,871 loops=1)

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

  • Filter: ("substring"(md5((part_number)::text), 1, 1) = '0'::text)
  • Rows Removed by Filter: 164260112
7. 6,403.130 14,078.121 ↑ 1.0 5,051,460 1

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

  • Buckets: 2097152 Batches: 8 Memory Usage: 49705kB
8. 7,674.991 7,674.991 ↑ 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.000..7,674.991 rows=5,051,460 loops=1)

9. 6,868.765 15,147.076 ↑ 1.0 5,110,816 1

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

  • Buckets: 1048576 Batches: 8 Memory Usage: 64959kB
10. 8,278.311 8,278.311 ↑ 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.260..8,278.311 rows=5,110,816 loops=1)

11. 0.053 0.091 ↑ 4.2 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.038 0.038 ↑ 4.2 33 1

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

13. 27.596 60.741 ↑ 1.0 18,535 1

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

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

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

Planning time : 8.366 ms
Execution time : 437,529.178 ms