explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8cZz

Settings
# exclusive inclusive rows x rows loops node
1. 1,915.422 11,094.198 ↓ 0.0 0 1

Update on in_contract_smart i (cost=442,770.64..457,760.42 rows=1 width=731) (actual time=11,094.198..11,094.198 rows=0 loops=1)

2. 24.033 9,178.776 ↓ 101,759.0 101,759 1

Nested Loop (cost=442,770.64..457,760.42 rows=1 width=731) (actual time=7,961.839..9,178.776 rows=101,759 loops=1)

3. 335.380 8,645.948 ↓ 101,759.0 101,759 1

Hash Right Join (cost=442,770.21..457,759.96 rows=1 width=757) (actual time=7,961.022..8,645.948 rows=101,759 loops=1)

  • Hash Cond: (ed.contract = c.contract)
  • Filter: ((cd.dossier IS NOT NULL) OR (ed.contract IS NOT NULL))
4. 111.177 1,155.130 ↓ 1.1 711,944 1

Subquery Scan on ed (cost=19,326.17..31,949.11 rows=631,147 width=40) (actual time=805.549..1,155.130 rows=711,944 loops=1)

5. 547.933 1,043.953 ↓ 1.1 711,944 1

HashAggregate (cost=19,326.17..25,637.64 rows=631,147 width=8) (actual time=805.523..1,043.953 rows=711,944 loops=1)

  • Group Key: x.contract
6. 261.870 496.020 ↓ 1.1 755,765 1

Hash Left Join (cost=2,341.68..17,594.33 rows=692,735 width=8) (actual time=41.796..496.020 rows=755,765 loops=1)

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

Seq Scan on o_edossier_contract x (cost=0.00..13,248.56 rows=763,456 width=16) (actual time=0.856..193.921 rows=763,456 loops=1)

8. 15.866 40.229 ↑ 1.0 80,297 1

Hash (cost=1,337.97..1,337.97 rows=80,297 width=12) (actual time=40.228..40.229 rows=80,297 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,191kB
9. 24.363 24.363 ↑ 1.0 80,297 1

Seq Scan on o_edossier_removed x2 (cost=0.00..1,337.97 rows=80,297 width=12) (actual time=0.029..24.363 rows=80,297 loops=1)

10. 86.331 7,155.438 ↓ 101,759.0 101,759 1

Hash (cost=423,444.03..423,444.03 rows=1 width=733) (actual time=7,155.438..7,155.438 rows=101,759 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 26,464kB
11. 80.670 7,069.107 ↓ 101,759.0 101,759 1

Nested Loop (cost=2.58..423,444.03 rows=1 width=733) (actual time=2,664.948..7,069.107 rows=101,759 loops=1)

  • Join Filter: ((i.contract_number)::text = (d.nomer)::text)
12. 0.000 6,479.642 ↓ 101,759.0 101,759 1

Nested Loop (cost=2.15..423,443.56 rows=1 width=719) (actual time=2,664.933..6,479.642 rows=101,759 loops=1)

  • Join Filter: ((i.iin)::text = (ci.iin)::text)
13. 5,890.110 5,890.110 ↓ 101,759.0 101,759 1

Nested Loop (cost=1.72..423,443.09 rows=1 width=705) (actual time=2,664.915..5,890.110 rows=101,759 loops=1)

14. 0.000 5,288.886 ↓ 101,759.0 101,759 1

Nested Loop Left Join (cost=1.29..423,442.62 rows=1 width=691) (actual time=2,664.896..5,288.886 rows=101,759 loops=1)

15. 88.529 4,685.277 ↓ 101,759.0 101,759 1

Nested Loop (cost=0.86..423,442.16 rows=1 width=677) (actual time=2,664.880..4,685.277 rows=101,759 loops=1)

16. 38.251 4,087.953 ↓ 101,759.0 101,759 1

Nested Loop (cost=0.43..423,441.71 rows=1 width=663) (actual time=2,664.860..4,087.953 rows=101,759 loops=1)

17. 2,726.835 2,726.835 ↓ 101,759.0 101,759 1

Seq Scan on in_contract_smart i (cost=0.00..423,439.05 rows=1 width=649) (actual time=2,664.814..2,726.835 rows=101,759 loops=1)

  • Filter: (migrate_status = '2'::numeric)
  • Rows Removed by Filter: 11,509,119
18. 1,322.867 1,322.867 ↑ 1.0 1 101,759

Index Scan using ocontract_contrdwhid_index on o_contract_contractdwhid cc (cost=0.43..2.65 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=101,759)

  • Index Cond: ((contractdwhid)::text = (i.contract_dwh_id)::text)
19. 508.795 508.795 ↑ 1.0 1 101,759

Index Scan using m_contract_pkey on m_contract c (cost=0.43..0.46 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=101,759)

  • Index Cond: (contract = cc.contract)
20. 610.554 610.554 ↑ 1.0 1 101,759

Index Scan using o_contract_dossier_pkey on o_contract_dossier cd (cost=0.43..0.46 rows=1 width=22) (actual time=0.006..0.006 rows=1 loops=101,759)

  • Index Cond: (contract = c.contract)
21. 508.795 508.795 ↑ 1.0 1 101,759

Index Scan using o_contract_stage_pkey on o_contract_stage cs (cost=0.43..0.46 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=101,759)

  • Index Cond: (contract = c.contract)
  • Filter: ((stage)::text = 'PTP'::text)
22. 508.795 508.795 ↑ 1.0 1 101,759

Index Scan using o_contract_iin_pkey on o_contract_iin ci (cost=0.43..0.46 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=101,759)

  • Index Cond: (contract = c.contract)
23. 508.795 508.795 ↑ 1.0 1 101,759

Index Scan using o_contract_nomer_pkey on o_contract_nomer d (cost=0.43..0.46 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=101,759)

  • Index Cond: (contract = c.contract)
24. 508.795 508.795 ↑ 1.0 1 101,759

Index Scan using gg_o_contract_migratedat on o_contract_migratedat ma (cost=0.43..0.46 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=101,759)

  • Index Cond: (contract = d.contract)
  • Filter: (migratedat IS NOT NULL)