explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yieD

Settings
# exclusive inclusive rows x rows loops node
1. 11,473.126 27,089,942.291 ↓ 0.0 0 1

Update on in_contract_smart i (cost=19,339.60..578,482.55 rows=1 width=731) (actual time=27,089,942.291..27,089,942.291 rows=0 loops=1)

2. 3,013,232.959 27,078,469.165 ↓ 116,553.0 116,553 1

Nested Loop Left Join (cost=19,339.60..578,482.55 rows=1 width=731) (actual time=3,571.445..27,078,469.165 rows=116,553 loops=1)

  • Join Filter: (ed.contract = c.contract)
  • Rows Removed by Join Filter: 54,916,851,541
  • Filter: ((cd.dossier IS NOT NULL) OR (ed.contract IS NOT NULL))
3. 537.266 332,364.687 ↓ 116,553.0 116,553 1

Nested Loop Left Join (cost=3.01..538,626.37 rows=1 width=707) (actual time=2,470.742..332,364.687 rows=116,553 loops=1)

4. 453.089 281,126.866 ↓ 116,553.0 116,553 1

Nested Loop (cost=2.58..538,625.91 rows=1 width=693) (actual time=2,470.724..281,126.866 rows=116,553 loops=1)

5. 698.404 262,025.297 ↓ 116,553.0 116,553 1

Nested Loop (cost=2.15..538,625.46 rows=1 width=719) (actual time=2,470.708..262,025.297 rows=116,553 loops=1)

  • Join Filter: ((i.contract_dwh_id)::text = (cc.contractdwhid)::text)
6. 584.366 203,866.264 ↓ 116,553.0 116,553 1

Nested Loop (cost=1.72..538,624.98 rows=1 width=705) (actual time=2,470.687..203,866.264 rows=116,553 loops=1)

7. 499.677 146,054.375 ↓ 116,553.0 116,553 1

Nested Loop (cost=1.29..538,624.52 rows=1 width=691) (actual time=2,470.667..146,054.375 rows=116,553 loops=1)

8. 646.467 101,380.732 ↓ 116,554.0 116,554 1

Nested Loop (cost=0.86..538,624.06 rows=1 width=677) (actual time=2,470.071..101,380.732 rows=116,554 loops=1)

  • Join Filter: ((i.iin)::text = (ci.iin)::text)
9. 405.131 72,295.089 ↓ 1.3 116,554 1

Nested Loop (cost=0.43..495,754.19 rows=90,787 width=663) (actual time=2,470.051..72,295.089 rows=116,554 loops=1)

10. 5,454.748 5,454.748 ↓ 1.3 116,553 1

Seq Scan on in_contract_smart i (cost=0.00..422,003.33 rows=89,335 width=649) (actual time=2,470.010..5,454.748 rows=116,553 loops=1)

  • Filter: (migrate_status = '2'::numeric)
  • Rows Removed by Filter: 11,611,222
11. 66,435.210 66,435.210 ↑ 1.0 1 116,553

Index Scan using ocontract_nomer_index on o_contract_nomer d (cost=0.43..0.82 rows=1 width=27) (actual time=0.566..0.570 rows=1 loops=116,553)

  • Index Cond: ((nomer)::text = (i.contract_number)::text)
12. 28,439.176 28,439.176 ↑ 1.0 1 116,554

Index Scan using o_contract_iin_pkey on o_contract_iin ci (cost=0.43..0.46 rows=1 width=27) (actual time=0.244..0.244 rows=1 loops=116,554)

  • Index Cond: (contract = d.contract)
13. 44,173.966 44,173.966 ↑ 1.0 1 116,554

Index Scan using gg_o_contract_migratedat on o_contract_migratedat ma (cost=0.43..0.46 rows=1 width=14) (actual time=0.379..0.379 rows=1 loops=116,554)

  • Index Cond: (contract = d.contract)
  • Filter: (migratedat IS NOT NULL)
14. 57,227.523 57,227.523 ↑ 1.0 1 116,553

Index Scan using o_contract_stage_pkey on o_contract_stage cs (cost=0.43..0.46 rows=1 width=14) (actual time=0.491..0.491 rows=1 loops=116,553)

  • Index Cond: (contract = d.contract)
  • Filter: ((stage)::text = 'PTP'::text)
15. 57,460.629 57,460.629 ↑ 1.0 1 116,553

Index Scan using o_contract_contractdwhid_pkey on o_contract_contractdwhid cc (cost=0.43..0.46 rows=1 width=32) (actual time=0.493..0.493 rows=1 loops=116,553)

  • Index Cond: (contract = d.contract)
16. 18,648.480 18,648.480 ↑ 1.0 1 116,553

Index Scan using m_contract_pkey on m_contract c (cost=0.43..0.46 rows=1 width=14) (actual time=0.160..0.160 rows=1 loops=116,553)

  • Index Cond: (contract = d.contract)
17. 50,700.555 50,700.555 ↑ 1.0 1 116,553

Index Scan using o_contract_dossier_pkey on o_contract_dossier cd (cost=0.43..0.46 rows=1 width=22) (actual time=0.435..0.435 rows=1 loops=116,553)

  • Index Cond: (contract = c.contract)
18. 7,757,767.680 23,732,871.519 ↑ 1.3 471,176 116,553

Subquery Scan on ed (cost=19,336.59..31,964.03 rows=631,372 width=40) (actual time=0.009..203.623 rows=471,176 loops=116,553)

19. 15,974,644.758 15,975,103.839 ↑ 1.3 471,176 116,553

HashAggregate (cost=19,336.59..25,650.31 rows=631,372 width=8) (actual time=0.008..137.063 rows=471,176 loops=116,553)

  • Group Key: x.contract
20. 245.326 459.081 ↓ 1.1 755,974 1

Hash Left Join (cost=2,346.06..17,604.14 rows=692,981 width=8) (actual time=46.480..459.081 rows=755,974 loops=1)

  • Hash Cond: (x.edossier = x2.edossier)
  • Filter: ((x2.removed IS NULL) OR (x2.removed <> 1))
  • Rows Removed by Filter: 7,691
21. 168.188 168.188 ↑ 1.0 763,665 1

Seq Scan on o_edossier_contract x (cost=0.00..13,253.28 rows=763,728 width=16) (actual time=0.569..168.188 rows=763,665 loops=1)

22. 14.792 45.567 ↑ 1.0 80,367 1

Hash (cost=1,340.47..1,340.47 rows=80,447 width=12) (actual time=45.567..45.567 rows=80,367 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,194kB
23. 30.775 30.775 ↑ 1.0 80,367 1

Seq Scan on o_edossier_removed x2 (cost=0.00..1,340.47 rows=80,447 width=12) (actual time=2.603..30.775 rows=80,367 loops=1)