explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kuef

Settings
# exclusive inclusive rows x rows loops node
1. 3.249 80,158.550 ↑ 7,290.2 1,860 1

Finalize GroupAggregate (cost=2,194,547.27..4,213,534.77 rows=13,559,746 width=137) (actual time=63,786.547..80,158.550 rows=1,860 loops=1)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, sum((t_fait_bov_mouvements_mvt_y2019m01.mvt_present)::integer), count(*)
  • Group Key: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_dim_race_rac.rac_type_de_production
2. 0.000 80,155.301 ↑ 2,126.8 5,313 1

Gather Merge (cost=2,194,547.27..3,738,943.67 rows=11,299,788 width=137) (actual time=63,784.575..80,155.301 rows=5,313 loops=1)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, (PARTIAL sum((t_fait_bov_mouvements_mvt_y2019m01.mvt_present)::integer)), (PARTIAL count(*))
  • Workers Planned: 2
  • Workers Launched: 2
3. 9,594.300 238,230.900 ↑ 3,190.2 1,771 3

Partial GroupAggregate (cost=2,193,547.24..2,433,667.74 rows=5,649,894 width=137) (actual time=63,329.138..79,410.300 rows=1,771 loops=3)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, PARTIAL sum((t_fait_bov_mouvements_mvt_y2019m01.mvt_present)::integer), PARTIAL count(*)
  • Group Key: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_dim_race_rac.rac_type_de_production
  • Worker 0: actual time=62883.053..78835.524 rows=1774 loops=1
  • Worker 1: actual time=63776.949..79973.613 rows=1759 loops=1
4. 204,011.841 228,636.600 ↓ 1.2 6,517,104 3

Sort (cost=2,193,547.24..2,207,671.98 rows=5,649,894 width=125) (actual time=63,328.464..76,212.200 rows=6,517,104 loops=3)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_present
  • Sort Key: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_dim_race_rac.rac_type_de_production
  • Sort Method: external merge Disk: 790792kB
  • Worker 0: Sort Method: external merge Disk: 782072kB
  • Worker 1: Sort Method: external merge Disk: 790896kB
  • Worker 0: actual time=62883.029..75667.819 rows=6470043 loops=1
  • Worker 1: actual time=63774.971..76751.029 rows=6541958 loops=1
5. 5,309.463 24,624.759 ↓ 1.2 6,517,104 3

Hash Join (cost=2,527.06..826,086.49 rows=5,649,894 width=125) (actual time=19.440..8,208.253 rows=6,517,104 loops=3)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_classe_age_cag.cag_libelle, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_present
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m01.mvt_id_age)::integer = (tj_cag_age.id_age)::integer)
  • Worker 0: actual time=17.258..8184.776 rows=6470043 loops=1
  • Worker 1: actual time=17.247..8244.462 rows=6541958 loops=1
6. 6,470.025 19,285.995 ↓ 2.6 6,529,617 3

Parallel Hash Join (cost=1,556.28..756,074.38 rows=2,508,709 width=97) (actual time=9.492..6,428.665 rows=6,529,617 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_present, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_age, t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_race_rac.rac_type_de_production
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m01.mvt_id_lieu_mvt)::integer = t_dim_geographique_geo.geo_id_geographique)
  • Worker 0: actual time=7.154..6415.196 rows=6482734 loops=1
  • Worker 1: actual time=7.156..6469.786 rows=6554955 loops=1
7. 4,014.324 12,788.475 ↓ 2.6 6,562,934 3

Hash Join (cost=2.46..747,934.50 rows=2,508,709 width=31) (actual time=0.075..4,262.825 rows=6,562,934 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_present, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_age, t_dim_race_rac.rac_type_de_production
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m01.mvt_id_race)::integer = t_dim_race_rac.rac_id_race)
  • Worker 0: actual time=0.091..4271.737 rows=6515454 loops=1
  • Worker 1: actual time=0.081..4311.369 rows=6589179 loops=1
8. 1,501.248 8,774.034 ↑ 1.2 6,563,236 3

Parallel Append (cost=0.00..727,240.01 rows=7,719,104 width=29) (actual time=0.016..2,924.678 rows=6,563,236 loops=3)

  • Worker 0: actual time=0.021..2928.451 rows=6515773 loops=1
  • Worker 1: actual time=0.016..2971.144 rows=6589461 loops=1
9. 7,272.786 7,272.786 ↑ 1.2 6,563,236 3

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m01 (cost=0.00..688,644.49 rows=7,719,104 width=29) (actual time=0.015..2,424.262 rows=6,563,236 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_present, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_race
  • Filter: (((t_fait_bov_mouvements_mvt_y2019m01.mvt_present)::integer = 1) AND ((t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date)::text = ANY ('{20190101,20190102,20190103,20190104,20190105,20190106,20190107,20190108,20190109,20190110,20190111,20190112,20190113,20190114,20190115,20190116,20190117,20190118,20190119,20190120,20190121,20190122,20190123,20190124,20190125,20190126,20190127,20190128,20190129,20190130,20190131}'::text[])))
  • Rows Removed by Filter: 453264
  • Worker 0: actual time=0.020..2417.398 rows=6515773 loops=1
  • Worker 1: actual time=0.015..2419.102 rows=6589461 loops=1
10. 0.048 0.117 ↑ 1.0 65 3

Hash (cost=1.65..1.65 rows=65 width=10) (actual time=0.038..0.039 rows=65 loops=3)

  • 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.046..0.046 rows=65 loops=1
  • Worker 1: actual time=0.040..0.041 rows=65 loops=1
11. 0.069 0.069 ↑ 1.0 65 3

Seq Scan on entrepot.t_dim_race_rac (cost=0.00..1.65 rows=65 width=10) (actual time=0.014..0.023 rows=65 loops=3)

  • Output: t_dim_race_rac.rac_type_de_production, t_dim_race_rac.rac_id_race
  • Worker 0: actual time=0.021..0.029 rows=65 loops=1
  • Worker 1: actual time=0.016..0.024 rows=65 loops=1
12. 15.351 27.495 ↑ 1.8 13,192 3

Parallel Hash (cost=1,262.81..1,262.81 rows=23,281 width=74) (actual time=9.165..9.165 rows=13,192 loops=3)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_geographique_geo.geo_id_geographique
  • Buckets: 65536 Batches: 1 Memory Usage: 4864kB
  • Worker 0: actual time=6.997..6.997 rows=8711 loops=1
  • Worker 1: actual time=7.020..7.020 rows=8943 loops=1
13. 12.144 12.144 ↑ 1.8 13,192 3

Parallel Seq Scan on entrepot.t_dim_geographique_geo (cost=0.00..1,262.81 rows=23,281 width=74) (actual time=0.012..4.048 rows=13,192 loops=3)

  • Output: t_dim_geographique_geo.geo_union_europeenne, t_dim_geographique_geo.geo_pays, t_dim_geographique_geo.geo_code_region, t_dim_geographique_geo.geo_libelle_region, t_dim_geographique_geo.geo_code_departement, t_dim_geographique_geo.geo_libelle_departement, t_dim_geographique_geo.geo_id_geographique
  • Worker 0: actual time=0.012..3.201 rows=8711 loops=1
  • Worker 1: actual time=0.018..3.261 rows=8943 loops=1
14. 4.128 29.301 ↑ 2.3 7,501 3

Hash (cost=759.59..759.59 rows=16,895 width=36) (actual time=9.767..9.767 rows=7,501 loops=3)

  • Output: tj_cag_age.id_age, t_dim_classe_age_cag.cag_libelle
  • Buckets: 32768 Batches: 1 Memory Usage: 638kB
  • Worker 0: actual time=9.906..9.906 rows=7501 loops=1
  • Worker 1: actual time=9.894..9.895 rows=7501 loops=1
15. 13.752 25.173 ↑ 2.3 7,501 3

Hash Join (cost=1.60..759.59 rows=16,895 width=36) (actual time=0.109..8.391 rows=7,501 loops=3)

  • Output: tj_cag_age.id_age, t_dim_classe_age_cag.cag_libelle
  • Inner Unique: true
  • Hash Cond: ((tj_cag_age.id_cag)::integer = t_dim_classe_age_cag.cag_id)
  • Worker 0: actual time=0.122..8.527 rows=7501 loops=1
  • Worker 1: actual time=0.124..8.552 rows=7501 loops=1
16. 11.307 11.307 ↑ 1.0 43,177 3

Seq Scan on entrepot.tj_cag_age (cost=0.00..623.77 rows=43,177 width=8) (actual time=0.027..3.769 rows=43,177 loops=3)

  • Output: tj_cag_age.id_age, tj_cag_age.id_cag
  • Worker 0: actual time=0.026..3.889 rows=43177 loops=1
  • Worker 1: actual time=0.038..3.939 rows=43177 loops=1
17. 0.018 0.114 ↑ 1.0 9 3

Hash (cost=1.49..1.49 rows=9 width=36) (actual time=0.038..0.038 rows=9 loops=3)

  • Output: t_dim_classe_age_cag.cag_libelle, t_dim_classe_age_cag.cag_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.052..0.052 rows=9 loops=1
  • Worker 1: actual time=0.042..0.042 rows=9 loops=1
18. 0.096 0.096 ↑ 1.0 9 3

Seq Scan on entrepot.t_dim_classe_age_cag (cost=0.00..1.49 rows=9 width=36) (actual time=0.025..0.032 rows=9 loops=3)

  • Output: t_dim_classe_age_cag.cag_libelle, t_dim_classe_age_cag.cag_id
  • Filter: ((t_dim_classe_age_cag.cag_libelle)::text = ANY ('{"0 - 7 mois","7 mois - 9 mois","9 mois - 12 mois","12 mois - 14 mois","14 mois - 16 mois","16 mois - 20 mois","20 mois - 25 mois","25 mois - 36 mois","36 mois et plus"}'::text[]))
  • Rows Removed by Filter: 14
  • Worker 0: actual time=0.039..0.045 rows=9 loops=1