explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1HS

Settings
# exclusive inclusive rows x rows loops node
1. 27.697 37,982.544 ↑ 1.0 35,574 1

Finalize GroupAggregate (cost=539,590.25..546,267.16 rows=36,431 width=204) (actual time=37,359.092..37,982.544 rows=35,574 loops=1)

  • 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_date_dat.dat_annee, t_dim_date_dat.dat_semestre_avec_annee, t_dim_date_dat.dat_trimestre_avec_annee, t_dim_date_dat.dat_mois_avec_annee, t_dim_date_dat.dat_lib_mois_avec_annee, t_dim_date_dat.dat_mois_sans_annee, t_dim_race_rac.rac_cat_raciale, t_dim_race_rac.rac_cat_raciale_dominante, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_sexe, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_entree, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_sortie
2. 409.685 37,954.847 ↓ 2.4 72,929 1

Gather Merge (cost=539,590.25..544,157.15 rows=30,360 width=204) (actual time=37,359.063..37,954.847 rows=72,929 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 392.392 37,545.162 ↓ 1.6 24,310 3 / 3

Partial GroupAggregate (cost=538,590.23..539,652.83 rows=15,180 width=204) (actual time=37,098.131..37,545.162 rows=24,310 loops=3)

  • 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_date_dat.dat_annee, t_dim_date_dat.dat_semestre_avec_annee, t_dim_date_dat.dat_trimestre_avec_annee, t_dim_date_dat.dat_mois_avec_annee, t_dim_date_dat.dat_lib_mois_avec_annee, t_dim_date_dat.dat_mois_sans_annee, t_dim_race_rac.rac_cat_raciale, t_dim_race_rac.rac_cat_raciale_dominante, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_sexe, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_entree, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_sortie
4. 6,777.717 37,152.770 ↓ 28.9 438,588 3 / 3

Sort (cost=538,590.23..538,628.18 rows=15,180 width=172) (actual time=37,098.029..37,152.770 rows=438,588 loops=3)

  • 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_date_dat.dat_annee, t_dim_date_dat.dat_semestre_avec_annee, t_dim_date_dat.dat_trimestre_avec_annee, t_dim_date_dat.dat_mois_avec_annee, t_dim_date_dat.dat_lib_mois_avec_annee, t_dim_date_dat.dat_mois_sans_annee, t_dim_race_rac.rac_cat_raciale, t_dim_race_rac.rac_cat_raciale_dominante, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_sexe, t_dim_classe_age_cag.cag_libelle, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_entree, t_fait_bov_mouvements_mvt_y2019m06.mvt_id_cause_mvt_sortie
  • Sort Method: quicksort Memory: 132,551kB
  • Worker 0: Sort Method: quicksort Memory: 130,897kB
  • Worker 1: Sort Method: quicksort Memory: 122,917kB
5. 147.192 30,375.053 ↓ 28.9 438,588 3 / 3

Hash Join (cost=2,536.73..537,535.98 rows=15,180 width=172) (actual time=29,546.419..30,375.053 rows=438,588 loops=3)

  • Hash Cond: (t_fait_bov_mouvements_mvt_y2019m06.mvt_id_date = (t_dim_date_dat.dat_id_date)::integer)
6. 159.776 30,214.478 ↓ 28.9 438,588 3 / 3

Parallel Hash Join (cost=1,776.60..536,736.00 rows=15,180 width=132) (actual time=29,532.834..30,214.478 rows=438,588 loops=3)

  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m06.mvt_id_lieu_mvt)::integer = t_dim_geographique_geo.geo_id_geographique)
7. 131.048 29,448.861 ↓ 28.9 438,588 3 / 3

Hash Join (cost=222.31..535,141.86 rows=15,180 width=66) (actual time=28,926.777..29,448.861 rows=438,588 loops=3)

  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m06.mvt_id_age)::smallint = (tj_cag_age.id_age)::integer)
8. 118.328 29,241.183 ↓ 2.3 441,787 3 / 3

Hash Join (cost=2.62..534,052.73 rows=191,375 width=36) (actual time=28,850.121..29,241.183 rows=441,787 loops=3)

  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2019m06.mvt_id_race)::smallint = t_dim_race_rac.rac_id_race)
9. 36.032 29,121.869 ↑ 1.2 441,787 3 / 3

Parallel Append (cost=0.00..532,625.10 rows=531,596 width=26) (actual time=28,849.066..29,121.869 rows=441,787 loops=3)

10. 29,085.837 29,085.837 ↑ 1.2 441,787 3 / 3

Parallel Seq Scan on t_fait_bov_mouvements_mvt_y2019m06 (cost=0.00..529,967.12 rows=531,596 width=26) (actual time=28,849.065..29,085.837 rows=441,787 loops=3)

  • Filter: ((mvt_id_date >= 20,190,601) AND (mvt_id_date <= 20,190,630) AND (((mvt_entre_dans_le_mois)::smallint = 1) OR ((mvt_sorti_dans_le_mois)::smallint = 1)))
  • Rows Removed by Filter: 6,164,304
11. 0.021 0.986 ↑ 1.0 72 3 / 3

Hash (cost=1.72..1.72 rows=72 width=16) (actual time=0.984..0.986 rows=72 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
12. 0.965 0.965 ↑ 1.0 72 3 / 3

Seq Scan on t_dim_race_rac (cost=0.00..1.72 rows=72 width=16) (actual time=0.955..0.965 rows=72 loops=3)

13. 1.665 76.630 ↓ 12.6 8,485 3 / 3

Hash (cost=211.28..211.28 rows=673 width=36) (actual time=76.630..76.630 rows=8,485 loops=3)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 556kB
14. 1.896 74.965 ↓ 12.6 8,485 3 / 3

Hash Join (cost=27.97..211.28 rows=673 width=36) (actual time=56.825..74.965 rows=8,485 loops=3)

  • Hash Cond: ((tj_cag_age.id_cag)::integer = t_dim_classe_age_cag.cag_id)
15. 71.856 71.856 ↑ 1.0 8,486 3 / 3

Seq Scan on tj_cag_age (cost=0.00..160.86 rows=8,486 width=8) (actual time=55.584..71.856 rows=8,486 loops=3)

16. 0.009 1.213 ↑ 2.9 16 3 / 3

Hash (cost=27.40..27.40 rows=46 width=36) (actual time=1.213..1.213 rows=16 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 1.204 1.204 ↑ 2.9 16 3 / 3

Seq Scan on t_dim_classe_age_cag (cost=0.00..27.40 rows=46 width=36) (actual time=1.197..1.204 rows=16 loops=3)

  • Filter: ((cag_libelle)::text = ANY ('{"0 - 21 jours","21 jours - 3 mois","3 mois - 6 mois","6 mois - 8 mois","8 mois - 9 mois","9 mois - 12 mois","12 mois - 14 mois","14 mois - 16 mois","16 mois - 18 mois","18 mois - 20 mois","20 mois - 24 mois","24 mois - 25 mois","25 mois - 36 mois","36 mois - 8 ans","8 ans - 10 ans","10 ans et plus"}'::text[]))
  • Rows Removed by Filter: 2
18. 6.575 605.841 ↑ 1.8 13,142 3 / 3

Parallel Hash (cost=1,265.24..1,265.24 rows=23,124 width=74) (actual time=605.841..605.841 rows=13,142 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,864kB
19. 599.266 599.266 ↑ 1.8 13,142 3 / 3

Parallel Seq Scan on t_dim_geographique_geo (cost=0.00..1,265.24 rows=23,124 width=74) (actual time=1.564..599.266 rows=13,142 loops=3)

20. 5.584 13.383 ↑ 1.0 18,628 3 / 3

Hash (cost=527.28..527.28 rows=18,628 width=48) (actual time=13.383..13.383 rows=18,628 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,754kB
21. 7.799 7.799 ↑ 1.0 18,628 3 / 3

Seq Scan on t_dim_date_dat (cost=0.00..527.28 rows=18,628 width=48) (actual time=0.028..7.799 rows=18,628 loops=3)

Planning time : 7,090.873 ms
Execution time : 38,031.738 ms