explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uRMw : Optimization for: Optimization for: Optimization for: Optimization for: plan #QGfd; plan #jRPk; plan #vS9q4; plan #y2pD

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,191.225 95,880.718 ↓ 12.6 10,410,167 1

Unique (cost=2,851,844.78..2,891,053.46 rows=825,446 width=278) (actual time=62,575.980..95,880.718 rows=10,410,167 loops=1)

2. 67,325.462 91,689.493 ↓ 12.6 10,410,167 1

Sort (cost=2,851,844.78..2,853,908.39 rows=825,446 width=278) (actual time=62,575.979..91,689.493 rows=10,410,167 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: 1398584kB
3. 2,155.238 24,364.031 ↓ 12.6 10,410,167 1

Hash Left Join (cost=580,465.37..2,663,512.84 rows=825,446 width=278) (actual time=6,904.011..24,364.031 rows=10,410,167 loops=1)

  • Hash Cond: (ldai.part_number = ano.snr)
4. 2,067.173 22,202.916 ↓ 12.6 10,410,167 1

Hash Join (cost=579,802.06..2,658,111.15 rows=825,446 width=193) (actual time=6,898.004..22,202.916 rows=10,410,167 loops=1)

  • Hash Cond: (ldai.plant = p.plant_code_psdh)
5. 5,991.629 20,135.718 ↓ 12.6 10,439,237 1

Hash Left Join (cost=579,788.91..2,646,748.12 rows=825,446 width=105) (actual time=6,897.962..20,135.718 rows=10,439,237 loops=1)

  • Hash Cond: (ldai.part_number = tt.snr)
6. 5,163.297 12,071.036 ↓ 12.6 10,439,237 1

Hash Left Join (cost=255,142.25..2,234,604.35 rows=825,446 width=57) (actual time=4,822.213..12,071.036 rows=10,439,237 loops=1)

  • Hash Cond: (ldai.part_number = apr.snr)
7. 2,125.813 4,960.438 ↓ 12.6 10,439,237 1

Bitmap Heap Scan on lterm_date_ai ldai (cost=17,517.39..1,937,729.38 rows=825,446 width=46) (actual time=2,870.008..4,960.438 rows=10,439,237 loops=1)

  • Recheck Cond: ((md5hash >= '0'::text) AND (md5hash < '1'::text))
  • Heap Blocks: exact=212497
8. 2,834.625 2,834.625 ↓ 12.6 10,439,237 1

Bitmap Index Scan on lterm_date_ai_md5hash_idx (cost=0.00..17,311.03 rows=825,446 width=0) (actual time=2,834.624..2,834.625 rows=10,439,237 loops=1)

  • Index Cond: ((md5hash >= '0'::text) AND (md5hash < '1'::text))
9. 783.499 1,947.301 ↑ 1.0 4,994,216 1

Hash (cost=145,934.16..145,934.16 rows=4,994,216 width=22) (actual time=1,947.301..1,947.301 rows=4,994,216 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 49332kB
10. 1,163.802 1,163.802 ↑ 1.0 4,994,216 1

Seq Scan on tzt_vtzapr apr (cost=0.00..145,934.16 rows=4,994,216 width=22) (actual time=0.016..1,163.802 rows=4,994,216 loops=1)

11. 927.387 2,073.053 ↑ 1.0 5,053,318 1

Hash (cost=207,196.18..207,196.18 rows=5,053,318 width=59) (actual time=2,073.053..2,073.053 rows=5,053,318 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 64309kB
12. 1,145.666 1,145.666 ↑ 1.0 5,053,318 1

Seq Scan on tzt_vtzate tt (cost=0.00..207,196.18 rows=5,053,318 width=59) (actual time=0.010..1,145.666 rows=5,053,318 loops=1)

13. 0.015 0.025 ↑ 4.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.010 0.010 ↑ 4.0 35 1

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

15. 2.653 5.877 ↑ 1.0 18,503 1

Hash (cost=432.03..432.03 rows=18,503 width=22) (actual time=5.877..5.877 rows=18,503 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1232kB
16. 3.224 3.224 ↑ 1.0 18,503 1

Seq Scan on tzt_vtzano ano (cost=0.00..432.03 rows=18,503 width=22) (actual time=0.009..3.224 rows=18,503 loops=1)

Planning time : 0.827 ms
Execution time : 96,276.488 ms