explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CWGx

Settings
# exclusive inclusive rows x rows loops node
1. 140.153 21,430.448 ↑ 1.0 1 1

Aggregate (cost=719,795.68..719,795.69 rows=1 width=0) (actual time=21,430.447..21,430.448 rows=1 loops=1)

2.          

CTE cte1_physique

3. 425.412 20,627.493 ↓ 500,000.0 500,000 1

Nested Loop (cost=23,068.73..73,490.51 rows=1 width=16) (actual time=356.132..20,627.493 rows=500,000 loops=1)

  • Join Filter: ((t.id_de = d.id_de) AND ((t.tc)::text = (d.tc)::text))
4. 528.068 19,202.081 ↓ 500,000.0 500,000 1

Nested Loop (cost=23,068.44..73,490.18 rows=1 width=35) (actual time=356.096..19,202.081 rows=500,000 loops=1)

5. 1,607.098 2,174.013 ↓ 500,000.0 500,000 1

Hash Join (cost=23,068.00..73,488.01 rows=1 width=27) (actual time=355.491..2,174.013 rows=500,000 loops=1)

  • Hash Cond: (((phy.itip)::text = (t.itip)::text) AND ((phy.spi)::text = (t.spi)::text))
6. 212.087 212.087 ↑ 1.0 1,000,000 1

Seq Scan on pp phy (cost=0.00..17,353.00 rows=1,000,000 width=32) (actual time=0.008..212.087 rows=1,000,000 loops=1)

7. 198.166 354.828 ↑ 1.0 500,000 1

Hash (cost=11,173.00..11,173.00 rows=500,000 width=43) (actual time=354.828..354.828 rows=500,000 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 4618kB
8. 156.662 156.662 ↑ 1.0 500,000 1

Seq Scan on eirrt t (cost=0.00..11,173.00 rows=500,000 width=43) (actual time=0.013..156.662 rows=500,000 loops=1)

  • Filter: ((spi IS NOT NULL) AND (itip IS NOT NULL))
9. 16,500.000 16,500.000 ↑ 1.0 1 500,000

Index Scan using ietindex on iet ie (cost=0.43..2.16 rows=1 width=24) (actual time=0.026..0.033 rows=1 loops=500,000)

  • Index Cond: (id_ic = t.id_ic)
10. 1,000.000 1,000.000 ↑ 1.0 1 500,000

Index Scan using d_pk on ddd d (cost=0.29..0.31 rows=1 width=19) (actual time=0.001..0.002 rows=1 loops=500,000)

  • Index Cond: (id_ddd = ie.id_ddd)
11.          

CTE update_individu_enrichi

12. 28,002.695 49,925.211 ↓ 0.0 0 1

Update on iet ie_1 (cost=24,885.56..646,296.52 rows=10 width=84) (actual time=49,925.211..49,925.211 rows=0 loops=1)

13. 406.269 21,922.516 ↓ 50,000.0 500,000 1

Nested Loop (cost=24,885.56..646,296.52 rows=10 width=84) (actual time=10,760.274..21,922.516 rows=500,000 loops=1)

14. 550.977 16,516.247 ↓ 50,000.0 500,000 1

Hash Join (cost=24,885.12..646,291.29 rows=10 width=60) (actual time=10,760.219..16,516.247 rows=500,000 loops=1)

  • Hash Cond: ((ie_2.id_ddd = d_1.id_ddd) AND (t_1.id_de = d_1.id_de) AND ((t_1.tc)::text = (d_1.tc)::text))
15. 11,493.012 15,891.749 ↑ 1.0 500,000 1

Hash Join (cost=22,795.00..629,201.07 rows=500,000 width=73) (actual time=10,686.639..15,891.749 rows=500,000 loops=1)

  • Hash Cond: (ie_2.id_ic = t_1.id_ic)
16. 4,010.255 4,010.255 ↓ 1.0 14,517,700 1

Seq Scan on iet ie_2 (cost=0.00..293,189.61 rows=14,304,061 width=30) (actual time=0.016..4,010.255 rows=14,517,700 loops=1)

17. 209.846 388.482 ↑ 1.0 500,000 1

Hash (cost=11,173.00..11,173.00 rows=500,000 width=59) (actual time=388.482..388.482 rows=500,000 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 4601kB
18. 178.636 178.636 ↑ 1.0 500,000 1

Seq Scan on eirrt t_1 (cost=0.00..11,173.00 rows=500,000 width=59) (actual time=0.013..178.636 rows=500,000 loops=1)

19. 18.514 73.521 ↓ 1.0 51,192 1

Hash (cost=1,194.68..1,194.68 rows=51,168 width=25) (actual time=73.521..73.521 rows=51,192 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2950kB
20. 55.007 55.007 ↓ 1.0 51,192 1

Seq Scan on ddd d_1 (cost=0.00..1,194.68 rows=51,168 width=25) (actual time=0.015..55.007 rows=51,192 loops=1)

21. 5,000.000 5,000.000 ↑ 1.0 1 500,000

Index Scan using iet_pk on iet ie_1 (cost=0.43..0.51 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=500,000)

  • Index Cond: (id_ie = ie_2.id_ie)
22.          

CTE insertion_e_pers_bbb

23. 10,047.990 15,331.476 ↓ 0.0 0 1

Insert on pers_bbb_test (cost=0.44..8.62 rows=2 width=16) (actual time=15,331.476..15,331.476 rows=0 loops=1)

24. 623.632 5,283.486 ↓ 500,000.0 1,000,000 1

Nested Loop (cost=0.44..8.62 rows=2 width=16) (actual time=0.116..5,283.486 rows=1,000,000 loops=1)

25. 159.854 159.854 ↓ 500,000.0 500,000 1

CTE Scan on cte1_physique ip (cost=0.00..0.02 rows=1 width=16) (actual time=0.019..159.854 rows=500,000 loops=1)

26. 4,500.000 4,500.000 ↑ 1.0 2 500,000

Index Scan using biindex on bbb bul (cost=0.44..8.58 rows=2 width=16) (actual time=0.006..0.009 rows=2 loops=500,000)

  • Index Cond: (id_ie = ip.id_ie)
27. 21,290.295 21,290.295 ↓ 500,000.0 500,000 1

CTE Scan on cte1_physique (cost=0.00..0.02 rows=1 width=0) (actual time=356.137..21,290.295 rows=500,000 loops=1)