explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cZ9b

Settings
# exclusive inclusive rows x rows loops node
1. 43,748.100 79,187,340.638 ↓ 0.0 0 1

Update on in_contract_smart i (cost=275,423.21..779,344.21 rows=1 width=735) (actual time=79,187,340.638..79,187,340.638 rows=0 loops=1)

2. 9,137,408.074 79,143,592.538 ↓ 339,040.0 339,040 1

Nested Loop Left Join (cost=275,423.21..779,344.21 rows=1 width=735) (actual time=4,990.517..79,143,592.538 rows=339,040 loops=1)

  • Join Filter: (ed.contract = c.contract)
  • Rows Removed by Join Filter: 189,596,987,018
  • Filter: ((cd.dossier IS NOT NULL) OR (ed.contract IS NOT NULL))
3. 1,025.332 144,619.184 ↓ 339,040.0 339,040 1

Nested Loop Left Join (cost=255,791.49..738,803.45 rows=1 width=711) (actual time=4,263.051..144,619.184 rows=339,040 loops=1)

4. 1,146.488 117,487.772 ↓ 339,040.0 339,040 1

Nested Loop (cost=255,791.06..738,802.96 rows=1 width=697) (actual time=4,263.036..117,487.772 rows=339,040 loops=1)

5. 1,249.911 87,183.844 ↓ 339,040.0 339,040 1

Nested Loop (cost=255,790.63..738,802.47 rows=1 width=723) (actual time=4,263.023..87,183.844 rows=339,040 loops=1)

  • Join Filter: ((i.contract_dwh_id)::text = (cc.contractdwhid)::text)
6. 1,036.642 58,471.693 ↓ 339,040.0 339,040 1

Nested Loop (cost=255,790.20..738,801.96 rows=1 width=709) (actual time=4,263.005..58,471.693 rows=339,040 loops=1)

7. 1,613.289 31,551.579 ↓ 340,572.0 340,572 1

Nested Loop (cost=255,789.77..738,801.47 rows=1 width=695) (actual time=4,260.767..31,551.579 rows=340,572 loops=1)

8. 5,433.290 14,271.978 ↓ 340,572.0 340,572 1

Hash Join (cost=255,789.34..738,800.99 rows=1 width=681) (actual time=4,260.745..14,271.978 rows=340,572 loops=1)

  • Hash Cond: (((i.contract_number)::text = (d.nomer)::text) AND ((i.iin)::text = (ci.iin)::text))
9. 4,683.837 4,683.837 ↑ 1.0 339,040 1

Seq Scan on in_contract_smart i (cost=0.00..397,137.22 rows=351,342 width=653) (actual time=78.828..4,683.837 rows=339,040 loops=1)

  • Filter: (migrate_status = '2'::numeric)
  • Rows Removed by Filter: 11,055
10. 991.559 4,154.851 ↑ 1.0 2,460,032 1

Hash (cost=194,847.42..194,847.42 rows=2,460,728 width=54) (actual time=4,154.850..4,154.851 rows=2,460,032 loops=1)

  • Buckets: 1,048,576 Batches: 4 Memory Usage: 64,100kB
11. 1,875.150 3,163.292 ↓ 1.0 2,460,804 1

Hash Join (cost=92,716.38..194,847.42 rows=2,460,728 width=54) (actual time=905.820..3,163.292 rows=2,460,804 loops=1)

  • Hash Cond: (ci.contract = d.contract)
12. 383.527 383.527 ↓ 1.0 2,463,259 1

Seq Scan on o_contract_iin ci (cost=0.00..45,172.14 rows=2,462,814 width=27) (actual time=0.014..383.527 rows=2,463,259 loops=1)

13. 574.603 904.615 ↓ 1.0 2,461,077 1

Hash (cost=45,135.28..45,135.28 rows=2,460,728 width=27) (actual time=904.615..904.615 rows=2,461,077 loops=1)

  • Buckets: 1,048,576 Batches: 4 Memory Usage: 44,909kB
14. 330.012 330.012 ↓ 1.0 2,461,077 1

Seq Scan on o_contract_nomer d (cost=0.00..45,135.28 rows=2,460,728 width=27) (actual time=0.013..330.012 rows=2,461,077 loops=1)

15. 15,666.312 15,666.312 ↑ 1.0 1 340,572

Index Scan using m_contract_pkey on m_contract c (cost=0.43..0.48 rows=1 width=14) (actual time=0.046..0.046 rows=1 loops=340,572)

  • Index Cond: (contract = d.contract)
16. 25,883.472 25,883.472 ↑ 1.0 1 340,572

Index Scan using gg_o_contract_migratedat on o_contract_migratedat ma (cost=0.43..0.49 rows=1 width=14) (actual time=0.076..0.076 rows=1 loops=340,572)

  • Index Cond: (contract = d.contract)
  • Filter: (migratedat IS NOT NULL)
17. 27,462.240 27,462.240 ↑ 1.0 1 339,040

Index Scan using o_contract_contractdwhid_pkey on o_contract_contractdwhid cc (cost=0.43..0.49 rows=1 width=32) (actual time=0.081..0.081 rows=1 loops=339,040)

  • Index Cond: (contract = d.contract)
18. 29,157.440 29,157.440 ↑ 1.0 1 339,040

Index Scan using o_contract_stage_pkey on o_contract_stage cs (cost=0.43..0.50 rows=1 width=14) (actual time=0.086..0.086 rows=1 loops=339,040)

  • Index Cond: (contract = d.contract)
  • Filter: ((stage)::text = 'PTP'::text)
19. 26,106.080 26,106.080 ↑ 1.0 1 339,040

Index Scan using o_contract_dossier_pkey on o_contract_dossier cd (cost=0.43..0.49 rows=1 width=22) (actual time=0.077..0.077 rows=1 loops=339,040)

  • Index Cond: (contract = c.contract)
20. 29,948,420.320 69,861,565.280 ↑ 1.2 559,218 339,040

Subquery Scan on ed (cost=19,631.72..32,498.82 rows=643,355 width=40) (actual time=0.004..206.057 rows=559,218 loops=339,040)

21. 39,912,752.055 39,913,144.960 ↑ 1.2 559,218 339,040

HashAggregate (cost=19,631.72..26,065.27 rows=643,355 width=8) (actual time=0.003..117.724 rows=559,218 loops=339,040)

  • Group Key: x.contract
22. 216.152 392.905 ↓ 1.1 766,823 1

Hash Left Join (cost=2,402.97..17,884.17 rows=699,022 width=8) (actual time=32.648..392.905 rows=766,823 loops=1)

  • Hash Cond: (x.edossier = x2.edossier)
  • Filter: ((x2.removed IS NULL) OR (x2.removed <> 1))
  • Rows Removed by Filter: 8,180
23. 145.457 145.457 ↓ 1.0 775,003 1

Seq Scan on o_edossier_contract x (cost=0.00..13,447.26 rows=774,826 width=16) (actual time=0.718..145.457 rows=775,003 loops=1)

24. 13.240 31.296 ↓ 1.0 82,598 1

Hash (cost=1,374.10..1,374.10 rows=82,310 width=12) (actual time=31.296..31.296 rows=82,598 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,283kB
25. 18.056 18.056 ↓ 1.0 82,598 1

Seq Scan on o_edossier_removed x2 (cost=0.00..1,374.10 rows=82,310 width=12) (actual time=0.577..18.056 rows=82,598 loops=1)