explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sz2f

Settings
# exclusive inclusive rows x rows loops node
1. 493,981.421 14,433,432.990 ↑ 15.6 172,569,526 1

Finalize GroupAggregate (cost=95,644,469.57..542,090,049.22 rows=2,691,869,696 width=102) (actual time=7,037,433.366..14,433,432.990 rows=172,569,526 loops=1)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, sum((t_fait_bov_mouvements_mvt.mvt_present)::integer), sum((t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois)::integer), sum((t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois)::integer), count(*)
  • Group Key: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age
2. 0.000 13,939,451.569 ↑ 4.8 561,030,855 1

Gather Merge (cost=95,644,469.57..441,144,935.62 rows=2,691,869,696 width=102) (actual time=7,037,433.259..13,939,451.569 rows=561,030,855 loops=1)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, (PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_present)::integer)), (PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois)::integer)), (PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois)::integer)), (PARTIAL count(*))
  • Workers Planned: 8
  • Workers Launched: 7
3. 2,822,303.040 76,460,921.800 ↑ 4.8 70,128,857 8

Partial GroupAggregate (cost=95,643,469.43..109,102,817.91 rows=336,483,712 width=102) (actual time=7,032,683.488..9,557,615.225 rows=70,128,857 loops=8)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_present)::integer), PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois)::integer), PARTIAL sum((t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois)::integer), PARTIAL count(*)
  • Group Key: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age
  • Worker 0: actual time=7031974.337..9574450.444 rows=70013206 loops=1
  • Worker 1: actual time=7029587.045..9582620.262 rows=70557113 loops=1
  • Worker 2: actual time=7032418.826..9580313.486 rows=70593951 loops=1
  • Worker 3: actual time=7037051.665..9578922.734 rows=70504381 loops=1
  • Worker 4: actual time=7029437.839..9546087.224 rows=69338904 loops=1
  • Worker 5: actual time=7034239.217..9569328.340 rows=70122521 loops=1
  • Worker 6: actual time=7029329.805..9561508.464 rows=69954958 loops=1
4. 62,240,266.752 73,638,618.760 ↑ 1.0 334,745,151 8

Sort (cost=95,643,469.43..96,484,678.71 rows=336,483,712 width=82) (actual time=7,032,683.382..9,204,827.345 rows=334,745,151 loops=8)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, t_fait_bov_mouvements_mvt.mvt_present, t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois
  • Sort Key: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age
  • Sort Method: external merge Disk: 31278912kB
  • Worker 0: actual time=7031974.277..9219424.813 rows=334084025 loops=1
  • Worker 1: actual time=7029586.972..9226714.161 rows=336311757 loops=1
  • Worker 2: actual time=7032418.675..9225189.752 rows=336415150 loops=1
  • Worker 3: actual time=7037051.537..9225148.719 rows=334964743 loops=1
  • Worker 4: actual time=7029437.773..9196410.903 rows=332541018 loops=1
  • Worker 5: actual time=7034239.103..9215321.035 rows=336214553 loops=1
  • Worker 6: actual time=7029329.697..9208003.620 rows=334964730 loops=1
5. 1,382,514.960 11,398,352.008 ↑ 1.0 334,745,151 8

Hash Join (cost=1,900.92..38,556,599.99 rows=336,483,712 width=82) (actual time=230.900..1,424,794.001 rows=334,745,151 loops=8)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_fait_bov_mouvements_mvt.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, t_fait_bov_mouvements_mvt.mvt_present, t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt.mvt_id_race)::integer = t_dim_race_rac.rac_id_race)
  • Worker 0: actual time=234.219..1437423.367 rows=334084025 loops=1
  • Worker 1: actual time=239.212..1416926.734 rows=336311757 loops=1
  • Worker 2: actual time=220.280..1408040.752 rows=336415150 loops=1
  • Worker 3: actual time=227.855..1414890.850 rows=334964743 loops=1
  • Worker 4: actual time=197.722..1445659.498 rows=332541018 loops=1
  • Worker 5: actual time=245.327..1427809.183 rows=336214553 loops=1
  • Worker 6: actual time=235.125..1424619.854 rows=334964730 loops=1
6. 2,654,414.448 10,015,836.024 ↑ 1.0 334,750,484 8

Hash Join (cost=1,898.45..37,615,737.31 rows=336,483,712 width=80) (actual time=230.615..1,251,979.503 rows=334,750,484 loops=8)

  • Output: t_fait_bov_mouvements_mvt.mvt_id_date, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_age, t_fait_bov_mouvements_mvt.mvt_present, t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_id_race, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt.mvt_id_lieu_mvt)::integer = t_dim_geographique_geo.geo_id_geographique)
  • Worker 0: actual time=233.846..1260450.406 rows=334089450 loops=1
  • Worker 1: actual time=238.943..1244768.239 rows=336317066 loops=1
  • Worker 2: actual time=220.048..1234835.651 rows=336420300 loops=1
  • Worker 3: actual time=227.432..1243237.388 rows=334970096 loops=1
  • Worker 4: actual time=197.495..1272925.744 rows=332546132 loops=1
  • Worker 5: actual time=245.015..1254136.108 rows=336219948 loops=1
  • Worker 6: actual time=234.863..1253566.595 rows=334970063 loops=1
7. 7,360,445.608 7,360,445.608 ↑ 1.0 336,483,710 8

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt (cost=0.00..36,730,472.12 rows=336,483,712 width=37) (actual time=107.639..920,055.701 rows=336,483,710 loops=8)

  • Output: t_fait_bov_mouvements_mvt.mvt_id_exploitation_elevage, t_fait_bov_mouvements_mvt.mvt_id_date, t_fait_bov_mouvements_mvt.mvt_id_age_entree, t_fait_bov_mouvements_mvt.mvt_id_age, t_fait_bov_mouvements_mvt.mvt_id_age_entree_cag, t_fait_bov_mouvements_mvt.mvt_id_age_cag, t_fait_bov_mouvements_mvt.mvt_id_cause_mvt_entree, t_fait_bov_mouvements_mvt.mvt_id_cause_mvt_sortie, t_fait_bov_mouvements_mvt.mvt_id_origine_mvt, t_fait_bov_mouvements_mvt.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt.mvt_id_bovin, t_fait_bov_mouvements_mvt.mvt_id_sexe, t_fait_bov_mouvements_mvt.mvt_id_race, t_fait_bov_mouvements_mvt.mvt_id_velage, t_fait_bov_mouvements_mvt.mvt_id_exploitation_transhumanc, t_fait_bov_mouvements_mvt.mvt_id_age_depart_transhumance, t_fait_bov_mouvements_mvt.mvt_id_age_transhumance, t_fait_bov_mouvements_mvt.mvt_id_lieu_transhumance, t_fait_bov_mouvements_mvt.mvt_id_age_depart_transhumance_, t_fait_bov_mouvements_mvt.mvt_id_age_transhumance_cag, t_fait_bov_mouvements_mvt.mvt_id_destination_mvt, t_fait_bov_mouvements_mvt.mvt_id_lieu_abattage, t_fait_bov_mouvements_mvt.mvt_id_temps_abattage, t_fait_bov_mouvements_mvt.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_present, t_fait_bov_mouvements_mvt.mvt_nb_jours_pres_jours_mois, t_fait_bov_mouvements_mvt.mvt_parti_trans_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_revenu_trans_dans_le_mois, t_fait_bov_mouvements_mvt.mvt_en_transhumance, t_fait_bov_mouvements_mvt.mvt_nb_jours_trans_jours_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugb_tech_fin_de_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugb_tech_sur_jours_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbta_fin_de_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbta_sur_jours_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbpc_fin_de_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbeur_fin_de_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbeur_sur_jours_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbpc_sur_jours_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbag_fin_de_mois, t_fait_bov_mouvements_mvt.mvt_nb_ugbag_sur_jours_mois, t_fait_bov_mouvements_mvt.mvt_abattu, t_fait_bov_mouvements_mvt.mvt_poids_carcasse, t_fait_bov_mouvements_mvt.mvt_numero_job, t_fait_bov_mouvements_mvt.mvt_id_date_abattage
  • Worker 0: actual time=144.814..923482.018 rows=335844585 loops=1
  • Worker 1: actual time=133.711..906708.111 rows=338044361 loops=1
  • Worker 2: actual time=133.336..902803.583 rows=338145028 loops=1
  • Worker 3: actual time=143.602..912650.222 rows=336751171 loops=1
  • Worker 4: actual time=14.961..943850.997 rows=334266406 loops=1
  • Worker 5: actual time=137.505..921729.889 rows=337996443 loops=1
  • Worker 6: actual time=143.420..925446.489 rows=336617898 loops=1
8. 257.680 975.968 ↓ 1.0 39,577 8

Hash (cost=1,415.98..1,415.98 rows=38,598 width=51) (actual time=121.996..121.996 rows=39,577 loops=8)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_dim_geographique_geo.geo_id_geographique
  • Buckets: 65536 Batches: 1 Memory Usage: 3810kB
  • Worker 0: actual time=88.284..88.284 rows=39577 loops=1
  • Worker 1: actual time=104.310..104.310 rows=39577 loops=1
  • Worker 2: actual time=85.773..85.773 rows=39577 loops=1
  • Worker 3: actual time=82.737..82.738 rows=39577 loops=1
  • Worker 4: actual time=181.530..181.531 rows=39577 loops=1
  • Worker 5: actual time=106.583..106.583 rows=39577 loops=1
  • Worker 6: actual time=90.615..90.616 rows=39577 loops=1
9. 718.288 718.288 ↓ 1.0 39,577 8

Seq Scan on entrepot.t_dim_geographique_geo (cost=0.00..1,415.98 rows=38,598 width=51) (actual time=0.057..89.786 rows=39,577 loops=8)

  • Output: t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_libelle_departement, t_dim_geographique_geo.geo_id_geographique
  • Worker 0: actual time=0.052..54.812 rows=39577 loops=1
  • Worker 1: actual time=0.076..65.490 rows=39577 loops=1
  • Worker 2: actual time=0.056..63.262 rows=39577 loops=1
  • Worker 3: actual time=0.067..45.890 rows=39577 loops=1
  • Worker 4: actual time=0.052..155.310 rows=39577 loops=1
  • Worker 5: actual time=0.088..68.790 rows=39577 loops=1
  • Worker 6: actual time=0.046..56.856 rows=39577 loops=1
10. 0.456 1.024 ↑ 1.0 65 8

Hash (cost=1.65..1.65 rows=65 width=10) (actual time=0.128..0.128 rows=65 loops=8)

  • Output: t_dim_race_rac.rac_type_de_production, t_dim_race_rac.rac_id_race
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Worker 0: actual time=0.147..0.147 rows=65 loops=1
  • Worker 1: actual time=0.179..0.179 rows=65 loops=1
  • Worker 2: actual time=0.101..0.101 rows=65 loops=1
  • Worker 3: actual time=0.143..0.143 rows=65 loops=1
  • Worker 4: actual time=0.114..0.114 rows=65 loops=1
  • Worker 5: actual time=0.132..0.132 rows=65 loops=1
  • Worker 6: actual time=0.145..0.145 rows=65 loops=1
11. 0.568 0.568 ↑ 1.0 65 8

Seq Scan on entrepot.t_dim_race_rac (cost=0.00..1.65 rows=65 width=10) (actual time=0.050..0.071 rows=65 loops=8)

  • Output: t_dim_race_rac.rac_type_de_production, t_dim_race_rac.rac_id_race
  • Worker 0: actual time=0.062..0.094 rows=65 loops=1
  • Worker 1: actual time=0.051..0.069 rows=65 loops=1
  • Worker 2: actual time=0.045..0.062 rows=65 loops=1
  • Worker 3: actual time=0.067..0.086 rows=65 loops=1
  • Worker 4: actual time=0.049..0.067 rows=65 loops=1
  • Worker 5: actual time=0.048..0.066 rows=65 loops=1