explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1fXN : With scenarios Delete

Settings
# exclusive inclusive rows x rows loops node
1. 14,310.452 446,544.538 ↓ 4.8 7,488,432 1

Hash Join (cost=11,334,349.27..12,839,856.91 rows=1,565,316 width=265) (actual time=323,807.445..446,544.538 rows=7,488,432 loops=1)

  • Hash Cond: ("CMO2"."Region DTRH" = "CMO"."Region DTRH")
  • Buffers: shared hit=36 read=1617649 dirtied=5798 written=5723, temp read=527106 written=519172
2. 151.093 432,223.753 ↓ 4.8 367,080 1

Nested Loop (cost=11,334,345.97..12,637,014.79 rows=76,731 width=244) (actual time=323,797.020..432,223.753 rows=367,080 loops=1)

  • Join Filter: ("CMO2"."Region DTRH" = "P2"."Region")
  • Rows Removed by Join Filter: 1468320
  • Buffers: shared hit=36 read=1617648 dirtied=5798 written=5723, temp read=527106 written=519172
3. 140.833 291,848.100 ↓ 4.8 367,080 1

Hash Join (cost=5,987,110.84..7,053,831.24 rows=76,731 width=231) (actual time=245,650.034..291,848.100 rows=367,080 loops=1)

  • Hash Cond: ("P"."Region" = "CMO2"."Region DTRH")
  • Buffers: shared hit=4 read=813815 dirtied=5798 written=5723, temp read=425923 written=417989
4. 585.189 291,707.086 ↑ 8.4 367,080 1

Merge Left Join (cost=5,987,104.49..7,041,547.90 rows=3,069,247 width=218) (actual time=245,649.815..291,707.086 rows=367,080 loops=1)

  • Merge Cond: (("P".id_scenario = "B&R2".id_scenario) AND ("P"."Scenario" = "B&R2"."Scenario") AND ("P"."Annee" = "B&R2"."Annee") AND ("P"."Region" = "B&R2"."Region") AND ("P"."Metier" = "B&R2"."Metier") AND ("P"."Specialite" = "B&R2"."Specialite") AND ("P"."Libelle_spe" = "B&R2"."Libelle_spe"))
  • Buffers: shared hit=3 read=813815 dirtied=5798 written=5723, temp read=425923 written=417989
5. 18,639.081 288,386.404 ↑ 8.4 367,080 1

GroupAggregate (cost=5,960,485.88..6,930,006.71 rows=3,069,247 width=130) (actual time=243,039.975..288,386.404 rows=367,080 loops=1)

  • Group Key: "P".id_scenario, "P"."Scenario", "P"."Annee", "P"."Region", "P"."Metier", "P"."Specialite", "P"."Libelle_spe", "P"."Statut
  • Buffers: shared hit=3 read=803867 dirtied=5723 written=5723, temp read=406784 written=406784
6. 243,907.396 269,747.323 ↑ 1.1 22,024,800 1

Sort (cost=5,960,485.88..6,019,162.65 rows=23,470,709 width=130) (actual time=243,039.856..269,747.323 rows=22,024,800 loops=1)

  • Sort Key: "P".id_scenario, "P"."Scenario", "P"."Annee", "P"."Region", "P"."Metier", "P"."Specialite", "P"."Libelle_spe", "P"."Statut
  • Sort Method: external merge Disk: 3254152kB
  • Buffers: shared hit=3 read=803867 dirtied=5723 written=5723, temp read=406784 written=406784
7. 10,689.526 25,839.927 ↑ 1.1 22,024,800 1

Hash Semi Join (cost=4.05..1,482,714.16 rows=23,470,709 width=130) (actual time=116.638..25,839.927 rows=22,024,800 loops=1)

  • Hash Cond: ("P".id_scenario = ("S".id_scenario)::double precision)
  • Buffers: shared read=803867 dirtied=5723 written=5723
8. 15,149.808 15,149.808 ↓ 1.0 31,122,000 1

Seq Scan on "PresenceOD" "P" (cost=0.00..1,110,789.65 rows=30,692,465 width=130) (actual time=0.021..15,149.808 rows=31,122,000 loops=1)

  • Buffers: shared read=803865 dirtied=5723 written=5723
9. 0.031 0.593 ↓ 1.0 92 1

Hash (cost=2.91..2.91 rows=91 width=8) (actual time=0.593..0.593 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared read=2
10. 0.562 0.562 ↓ 1.0 92 1

Seq Scan on "SCENARIO" "S" (cost=0.00..2.91 rows=91 width=8) (actual time=0.542..0.562 rows=92 loops=1)

  • Buffers: shared read=2
11. 1,730.461 2,735.493 ↓ 17.1 442,889 1

Sort (cost=26,618.61..26,683.39 rows=25,914 width=159) (actual time=2,609.821..2,735.493 rows=442,889 loops=1)

  • Sort Key: "B&R2".id_scenario, "B&R2"."Scenario", "B&R2"."Annee", "B&R2"."Region", "B&R2"."Metier", "B&R2"."Specialite", "B&R2"."Libelle_spe
  • Sort Method: external sort Disk: 44816kB
  • Buffers: shared read=9948 dirtied=75, temp read=15172 written=11205
12. 34.575 1,005.032 ↓ 10.0 259,350 1

Subquery Scan on B&R2 (cost=24,200.64..24,718.92 rows=25,914 width=159) (actual time=765.133..1,005.032 rows=259,350 loops=1)

  • Buffers: shared read=9948 dirtied=75
13. 677.518 970.457 ↓ 10.0 259,350 1

HashAggregate (cost=24,200.64..24,459.78 rows=25,914 width=159) (actual time=765.131..970.457 rows=259,350 loops=1)

  • Group Key: "B&R".id_scenario, "B&R"."Scenario", "B&R"."Annee", "B&R"."Region", "B&R"."Metier", "B&R"."Specialite", "B&R"."Libelle_spe
  • Buffers: shared read=9948 dirtied=75
14. 292.939 292.939 ↓ 1.0 259,350 1

Seq Scan on "BesoinED" "B&R" (cost=0.00..12,539.39 rows=259,139 width=159) (actual time=0.760..292.939 rows=259,350 loops=1)

  • Buffers: shared read=9948 dirtied=75
15. 0.005 0.181 ↑ 1.0 5 1

Hash (cost=6.29..6.29 rows=5 width=13) (actual time=0.181..0.181 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
16. 0.000 0.176 ↑ 1.0 5 1

Subquery Scan on CMO2 (cost=5.42..6.29 rows=5 width=13) (actual time=0.103..0.176 rows=5 loops=1)

  • Buffers: shared hit=1
17. 0.113 0.176 ↑ 1.0 5 1

GroupAggregate (cost=5.42..6.24 rows=5 width=15) (actual time=0.103..0.176 rows=5 loops=1)

  • Group Key: "CMO_1"."Region DTRH
  • Buffers: shared hit=1
18. 0.051 0.063 ↑ 1.0 102 1

Sort (cost=5.42..5.68 rows=102 width=15) (actual time=0.059..0.063 rows=102 loops=1)

  • Sort Key: "CMO_1"."Region DTRH
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=1
19. 0.012 0.012 ↑ 1.0 102 1

Seq Scan on "CMO_Region_X_Y" "CMO_1" (cost=0.00..2.02 rows=102 width=15) (actual time=0.003..0.012 rows=102 loops=1)

  • Buffers: shared hit=1
20. 285.792 140,224.560 ↑ 1.0 5 367,080

Materialize (cost=5,347,235.13..5,577,428.74 rows=5 width=13) (actual time=0.213..0.382 rows=5 loops=367,080)

  • Buffers: shared hit=32 read=803833, temp read=101183 written=101183
21. 0.007 139,938.768 ↑ 1.0 5 1

Subquery Scan on P2 (cost=5,347,235.13..5,577,428.72 rows=5 width=13) (actual time=78,146.967..139,938.768 rows=5 loops=1)

  • Buffers: shared hit=32 read=803833, temp read=101183 written=101183
22. 49,824.018 139,938.761 ↑ 1.0 5 1

GroupAggregate (cost=5,347,235.13..5,577,428.67 rows=5 width=8) (actual time=78,146.966..139,938.761 rows=5 loops=1)

  • Group Key: "P_1"."Region
  • Buffers: shared hit=32 read=803833, temp read=101183 written=101183
23. 74,583.895 90,114.743 ↓ 1.0 31,122,000 1

Sort (cost=5,347,235.13..5,423,966.29 rows=30,692,465 width=8) (actual time=63,232.483..90,114.743 rows=31,122,000 loops=1)

  • Sort Key: "P_1"."Region
  • Sort Method: external merge Disk: 580944kB
  • Buffers: shared hit=32 read=803833, temp read=72648 written=72648
24. 15,530.848 15,530.848 ↓ 1.0 31,122,000 1

Seq Scan on "PresenceOD" "P_1" (cost=0.00..1,110,789.65 rows=30,692,465 width=8) (actual time=0.032..15,530.848 rows=31,122,000 loops=1)

  • Buffers: shared hit=32 read=803833
25. 0.035 10.333 ↑ 1.0 102 1

Hash (cost=2.02..2.02 rows=102 width=31) (actual time=10.333..10.333 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared read=1
26. 10.298 10.298 ↑ 1.0 102 1

Seq Scan on "CMO_Region_X_Y" "CMO" (cost=0.00..2.02 rows=102 width=31) (actual time=10.287..10.298 rows=102 loops=1)

  • Buffers: shared read=1