explain.depesz.com

PostgreSQL's explain analyze made readable

Result: esij

Settings
# exclusive inclusive rows x rows loops node
1. 6,919.015 804,140.230 ↑ 5.5 4,927,682 1

Finalize GroupAggregate (cost=2,921,343.28..6,877,884.00 rows=27,142,226 width=101) (actual time=584,218.786..804,140.230 rows=4,927,682 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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_present)::integer), sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois)::integer), sum((t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age
2. 0.000 797,221.215 ↑ 3.6 6,212,181 1

Gather Merge (cost=2,921,343.28..5,984,452.38 rows=22,618,522 width=101) (actual time=584,218.639..797,221.215 rows=6,212,181 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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, (PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_present)::integer)), (PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois)::integer)), (PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_entre_dans_le_mois)::integer)), (PARTIAL count(*))
  • Workers Planned: 2
  • Workers Launched: 2
3. 58,892.838 1,916,988.519 ↑ 5.5 2,070,727 3

Partial GroupAggregate (cost=2,920,343.26..3,372,713.70 rows=11,309,261 width=101) (actual time=560,763.092..638,996.173 rows=2,070,727 loops=3)

  • 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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_present)::integer), PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois)::integer), PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age
  • Worker 0: actual time=584184.884..672609.489 rows=1938199 loops=1
  • Worker 1: actual time=560297.745..641938.744 rows=2227253 loops=1
4. 1,011,068.829 1,858,095.681 ↓ 2.4 27,698,435 3

Sort (cost=2,920,343.26..2,948,616.41 rows=11,309,261 width=81) (actual time=560,762.968..619,365.227 rows=27,698,435 loops=3)

  • 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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age
  • Sort Method: external merge Disk: 2488192kB
  • Worker 0: Sort Method: external merge Disk: 2663920kB
  • Worker 1: Sort Method: external merge Disk: 2648760kB
  • Worker 0: actual time=584184.712..651857.549 rows=28384028 loops=1
  • Worker 1: actual time=560297.656..622275.044 rows=28216765 loops=1
5. 52,901.115 847,026.852 ↓ 2.4 27,698,435 3

Parallel Hash Join (cost=1,556.28..1,595,406.71 rows=11,309,261 width=81) (actual time=31.431..282,342.284 rows=27,698,435 loops=3)

  • 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_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.mvt_entre_dans_le_mois
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt)::integer = t_dim_geographique_geo.geo_id_geographique)
  • Worker 0: actual time=13.479..282550.259 rows=28384028 loops=1
  • Worker 1: actual time=26.906..286178.517 rows=28216765 loops=1
6. 36,694.398 794,073.249 ↓ 2.5 27,836,843 3

Hash Join (cost=2.46..1,564,162.90 rows=11,309,261 width=39) (actual time=13.467..264,691.083 rows=27,836,843 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt, t_dim_race_rac.rac_type_de_production
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_race)::integer = t_dim_race_rac.rac_id_race)
  • Worker 0: actual time=5.990..264420.035 rows=28529123 loops=1
  • Worker 1: actual time=19.431..268685.827 rows=28358680 loops=1
7. 12,325.926 757,378.620 ↑ 1.3 27,838,129 3

Parallel Append (cost=0.00..1,470,880.78 rows=34,797,725 width=37) (actual time=13.361..252,459.540 rows=27,838,129 loops=3)

  • Worker 0: actual time=5.867..251812.670 rows=28530430 loops=1
  • Worker 1: actual time=19.308..256424.926 rows=28359949 loops=1
8. 135,455.584 135,455.584 ↓ 2.4 21,380,632 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2018m12 (cost=0.00..331,997.97 rows=8,908,597 width=37) (actual time=19.306..135,455.584 rows=21,380,632 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_race
  • Worker 1: actual time=19.306..135455.584 rows=21380632 loops=1
9. 127,900.255 127,900.255 ↓ 2.4 21,049,498 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m01 (cost=0.00..326,856.24 rows=8,770,624 width=37) (actual time=5.865..127,900.255 rows=21,049,498 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2019m01.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m01.mvt_present, t_fait_bov_mouvements_mvt_y2019m01.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m01.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m01.mvt_id_race
  • Worker 0: actual time=5.865..127900.255 rows=21049498 loops=1
10. 337,252.959 337,252.959 ↑ 1.2 6,585,313 3

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2018m10 (cost=0.00..306,809.90 rows=8,231,490 width=37) (actual time=20.255..112,417.653 rows=6,585,313 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m10.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m10.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m10.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m10.mvt_present, t_fait_bov_mouvements_mvt_y2018m10.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m10.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m10.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m10.mvt_id_race
  • Worker 0: actual time=0.040..119436.985 rows=7480932 loops=1
  • Worker 1: actual time=30.364..108885.182 rows=6227588 loops=1
11. 126,811.576 126,811.576 ↓ 1.2 9,745,060 2

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m02 (cost=0.00..302,636.83 rows=8,120,883 width=37) (actual time=5.843..63,405.788 rows=9,745,060 loops=2)

  • Output: t_fait_bov_mouvements_mvt_y2019m02.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m02.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m02.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m02.mvt_present, t_fait_bov_mouvements_mvt_y2019m02.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m02.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m02.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m02.mvt_id_race
  • Worker 1: actual time=0.114..7932.854 rows=751729 loops=1
12. 17,632.318 17,632.318 ↓ 2.4 1,838,197 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2018m11 (cost=0.00..28,548.15 rows=765,915 width=37) (actual time=14.902..17,632.318 rows=1,838,197 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2018m11.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m11.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m11.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m11.mvt_present, t_fait_bov_mouvements_mvt_y2018m11.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m11.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2018m11.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m11.mvt_id_race
13. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m03 (cost=0.00..10.76 rows=76 width=62) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2019m03.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m03.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m03.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m03.mvt_present, t_fait_bov_mouvements_mvt_y2019m03.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m03.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m03.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m03.mvt_id_race
14. 0.000 0.000 ↓ 0.0 0 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m04 (cost=0.00..10.76 rows=76 width=62) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2019m04.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m04.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m04.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m04.mvt_present, t_fait_bov_mouvements_mvt_y2019m04.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m04.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m04.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m04.mvt_id_race
15. 0.000 0.000 ↓ 0.0 0 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m05 (cost=0.00..10.76 rows=76 width=62) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2019m05.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m05.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m05.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m05.mvt_present, t_fait_bov_mouvements_mvt_y2019m05.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m05.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m05.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m05.mvt_id_race
16. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2019m06 (cost=0.00..10.76 rows=76 width=62) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: t_fait_bov_mouvements_mvt_y2019m06.mvt_id_date, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_age, t_fait_bov_mouvements_mvt_y2019m06.mvt_present, t_fait_bov_mouvements_mvt_y2019m06.mvt_sorti_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m06.mvt_entre_dans_le_mois, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_race
17. 0.099 0.231 ↑ 1.0 65 3

Hash (cost=1.65..1.65 rows=65 width=10) (actual time=0.075..0.077 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.087..0.089 rows=65 loops=1
  • Worker 1: actual time=0.086..0.088 rows=65 loops=1
18. 0.132 0.132 ↑ 1.0 65 3

Seq Scan on entrepot.t_dim_race_rac (cost=0.00..1.65 rows=65 width=10) (actual time=0.027..0.044 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.035..0.052 rows=65 loops=1
  • Worker 1: actual time=0.033..0.050 rows=65 loops=1
19. 25.908 52.488 ↑ 1.8 13,192 3

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

  • 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: 3968kB
  • Worker 0: actual time=7.320..7.321 rows=4269 loops=1
  • Worker 1: actual time=7.312..7.312 rows=4158 loops=1
20. 26.580 26.580 ↑ 1.8 13,192 3

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

  • 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.022..3.627 rows=4269 loops=1