explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5KYC

Settings
# exclusive inclusive rows x rows loops node
1. 97.810 20,446.726 ↑ 1.0 1 1

Aggregate (cost=694,485.05..694,485.06 rows=1 width=0) (actual time=20,446.725..20,446.726 rows=1 loops=1)

2.          

CTE cte1

3. 319.828 15,228.769 ↓ 50,000.0 500,000 1

Hash Join (cost=26,349.12..694,306.78 rows=10 width=98) (actual time=9,155.035..15,228.769 rows=500,000 loops=1)

  • Hash Cond: ((ie.id_ddd = d.id_ddd) AND (t.id_ddd_collecte = d.id_ddd_collecte) AND ((t.type_collecte)::text = (d.type_collecte)::text))
4. 10,746.046 14,862.607 ↑ 1.0 500,000 1

Hash Join (cost=24,259.00..677,216.56 rows=500,000 width=101) (actual time=9,108.661..14,862.607 rows=500,000 loops=1)

  • Hash Cond: (ie.id_ic = t.id_ic)
5. 3,723.822 3,723.822 ↓ 1.0 14,517,700 1

Seq Scan on iet ie (cost=0.00..293,629.25 rows=14,348,025 width=24) (actual time=0.025..3,723.822 rows=14,517,700 loops=1)

6. 217.120 392.739 ↑ 1.0 500,000 1

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

  • Buckets: 8192 Batches: 8 Memory Usage: 5827kB
7. 175.619 175.619 ↑ 1.0 500,000 1

Seq Scan on eirrt t (cost=0.00..11,173.00 rows=500,000 width=85) (actual time=0.012..175.619 rows=500,000 loops=1)

8. 18.097 46.334 ↓ 1.0 51,192 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 2550kB
9. 28.237 28.237 ↓ 1.0 51,192 1

Seq Scan on ddd d (cost=0.00..1,194.68 rows=51,168 width=19) (actual time=0.009..28.237 rows=51,192 loops=1)

10.          

CTE cte1_physique

11. 370.905 20,064.511 ↓ 500,000.0 500,000 1

Nested Loop (cost=0.42..84.78 rows=1 width=16) (actual time=9,155.091..20,064.511 rows=500,000 loops=1)

12. 15,693.606 15,693.606 ↓ 50,000.0 500,000 1

CTE Scan on cte1 (cost=0.00..0.20 rows=10 width=88) (actual time=9,155.044..15,693.606 rows=500,000 loops=1)

  • Filter: ((spi IS NOT NULL) AND (itip IS NOT NULL))
13. 4,000.000 4,000.000 ↑ 1.0 1 500,000

Index Scan using ppsindex on pp phy (cost=0.42..8.45 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=500,000)

  • Index Cond: ((spi)::text = (cte1.spi)::text)
  • Filter: ((cte1.itip)::text = (itip)::text)
14.          

CTE update_ie

15. 35,181.665 41,316.493 ↓ 0.0 0 1

Update on iet ie_1 (cost=0.43..84.85 rows=10 width=132) (actual time=41,316.493..41,316.493 rows=0 loops=1)

16. 658.906 6,134.828 ↓ 50,000.0 500,000 1

Nested Loop (cost=0.43..84.85 rows=10 width=132) (actual time=0.695..6,134.828 rows=500,000 loops=1)

17. 475.922 475.922 ↓ 50,000.0 500,000 1

CTE Scan on cte1 cte1_1 (cost=0.00..0.20 rows=10 width=108) (actual time=0.586..475.922 rows=500,000 loops=1)

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

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

  • Index Cond: (id_ie = cte1_1.id_ie)
19.          

CTE iepb

20. 10,233.116 14,677.039 ↓ 0.0 0 1

Insert on pbt (cost=0.44..8.62 rows=2 width=16) (actual time=14,677.039..14,677.039 rows=0 loops=1)

21. 788.617 4,443.923 ↓ 500,000.0 1,000,000 1

Nested Loop (cost=0.44..8.62 rows=2 width=16) (actual time=0.154..4,443.923 rows=1,000,000 loops=1)

22. 155.306 155.306 ↓ 500,000.0 500,000 1

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

23. 3,500.000 3,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.007 rows=2 loops=500,000)

  • Index Cond: (id_ie = ip.id_ie)
24. 20,348.916 20,348.916 ↓ 500,000.0 500,000 1

CTE Scan on cte1_physique (cost=0.00..0.02 rows=1 width=0) (actual time=9,155.095..20,348.916 rows=500,000 loops=1)

Planning time : 3.504 ms
Execution time : 106,314.295 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint pers_bbb_bbb_test_fk on pbt 14,790.765 ms 1000000 0.015 ms
for constraint pers_bbb_pp_test_fk on pbt 14,441.789 ms 1000000 0.014 ms