explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uHAM : Ressource

Settings
# exclusive inclusive rows x rows loops node
1. 24,407.163 778,182.419 ↑ 12.8 7,000,056 1

Hash Join (cost=9,177,460.64..20,792,464.15 rows=89,462,923 width=268) (actual time=751,692.183..778,182.419 rows=7,000,056 loops=1)

  • Hash Cond: ("P2"."Region" = "CMO2"."Region DTRH")
2. 1,172.297 753,774.726 ↑ 12.8 343,140 1

Merge Right Join (cost=9,177,449.60..9,265,329.83 rows=4,385,442 width=234) (actual time=751,691.535..753,774.726 rows=343,140 loops=1)

  • Merge Cond: ((concat("B&R2".id_scenario, "B&R2"."Scenario", "B&R2"."Annee", "B&R2"."Region", "B&R2"."Metier", "B&R2"."Specialite", "B&R2"."Libelle_spe")) = (concat("P".id_scenario, "P"."Scenario", "P"."Annee", "P"."Region", "P"."Metier", "P"."Specialite", "P"."Libelle_spe")))
3. 5,423.125 7,507.688 ↓ 10.0 171,570 1

Sort (cost=17,590.42..17,633.27 rows=17,139 width=161) (actual time=6,917.722..7,507.688 rows=171,570 loops=1)

  • Sort Key: (concat("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 merge Disk: 41296kB
4. 868.718 2,084.563 ↓ 10.0 171,570 1

Subquery Scan on B&R2 (cost=16,042.34..16,385.12 rows=17,139 width=161) (actual time=836.588..2,084.563 rows=171,570 loops=1)

5. 1,122.712 1,215.845 ↓ 10.0 171,570 1

HashAggregate (cost=16,042.34..16,213.73 rows=17,139 width=161) (actual time=836.326..1,215.845 rows=171,570 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
6. 93.133 93.133 ↓ 1.0 171,570 1

Seq Scan on "BesoinED" "B&R" (cost=0.00..8,329.88 rows=171,388 width=161) (actual time=0.029..93.133 rows=171,570 loops=1)

7. 10,604.489 745,094.741 ↓ 6.7 343,140 1

Sort (cost=9,159,859.18..9,159,987.11 rows=51,175 width=146) (actual time=744,773.783..745,094.741 rows=343,140 loops=1)

  • Sort Key: (concat("P".id_scenario, "P"."Scenario", "P"."Annee", "P"."Region", "P"."Metier", "P"."Specialite", "P"."Libelle_spe"))
  • Sort Method: external sort Disk: 80440kB
8. 2,624.800 734,490.252 ↓ 6.7 343,140 1

Hash Join (cost=8,287,929.53..9,155,856.48 rows=51,175 width=146) (actual time=643,243.356..734,490.252 rows=343,140 loops=1)

  • Hash Cond: ("P"."Region" = "P2"."Region")
9. 27,720.295 570,981.364 ↑ 6.0 343,140 1

GroupAggregate (cost=4,626,929.28..5,466,198.26 rows=2,046,998 width=133) (actual time=482,359.152..570,981.364 rows=343,140 loops=1)

  • Group Key: "P".id_scenario, "P"."Scenario", "P"."Annee", "P"."Region", "P"."Metier", "P"."Specialite", "P"."Libelle_spe", "P"."Statut
10. 517,376.871 543,261.069 ↓ 1.0 20,588,400 1

Sort (cost=4,626,929.28..4,678,104.22 rows=20,469,975 width=133) (actual time=482,358.920..543,261.069 rows=20,588,400 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: 3076000kB
11. 25,884.198 25,884.198 ↓ 1.0 20,588,400 1

Seq Scan on "PresenceOD" "P" (cost=0.00..741,839.75 rows=20,469,975 width=133) (actual time=0.138..25,884.198 rows=20,588,400 loops=1)

12. 0.062 160,884.088 ↑ 1.0 5 1

Hash (cost=3,661,000.19..3,661,000.19 rows=5 width=13) (actual time=160,884.088..160,884.088 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.022 160,884.026 ↑ 1.0 5 1

Subquery Scan on P2 (cost=3,507,475.28..3,661,000.19 rows=5 width=13) (actual time=85,543.536..160,884.026 rows=5 loops=1)

14. 59,022.784 160,884.004 ↑ 1.0 5 1

GroupAggregate (cost=3,507,475.28..3,661,000.14 rows=5 width=8) (actual time=85,543.532..160,884.004 rows=5 loops=1)

  • Group Key: "P_1"."Region
15. 91,520.238 101,861.220 ↓ 1.0 20,588,400 1

Sort (cost=3,507,475.28..3,558,650.22 rows=20,469,975 width=8) (actual time=68,860.065..101,861.220 rows=20,588,400 loops=1)

  • Sort Key: "P_1"."Region
  • Sort Method: external merge Disk: 384328kB
16. 10,340.982 10,340.982 ↓ 1.0 20,588,400 1

Seq Scan on "PresenceOD" "P_1" (cost=0.00..741,839.75 rows=20,469,975 width=8) (actual time=0.206..10,340.982 rows=20,588,400 loops=1)

17. 0.059 0.530 ↑ 1.0 102 1

Hash (cost=9.77..9.77 rows=102 width=44) (actual time=0.530..0.530 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
18. 0.070 0.471 ↑ 1.0 102 1

Hash Join (cost=6.35..9.77 rows=102 width=44) (actual time=0.412..0.471 rows=102 loops=1)

  • Hash Cond: ("CMO"."Region DTRH" = "CMO2"."Region DTRH")
19. 0.042 0.042 ↑ 1.0 102 1

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

20. 0.007 0.359 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.001 0.352 ↑ 1.0 5 1

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

22. 0.217 0.351 ↑ 1.0 5 1

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

  • Group Key: "CMO_1"."Region DTRH
23. 0.111 0.134 ↑ 1.0 102 1

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

  • Sort Key: "CMO_1"."Region DTRH
  • Sort Method: quicksort Memory: 30kB
24. 0.023 0.023 ↑ 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.005..0.023 rows=102 loops=1)