explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rWoU

Settings
# exclusive inclusive rows x rows loops node
1. 3.059 216,505.749 ↑ 1,211.5 1,842 1

Finalize GroupAggregate (cost=694,187.74..1,026,448.93 rows=2,231,504 width=137) (actual time=200,352.419..216,505.749 rows=1,842 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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, sum((t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production
2. 0.000 216,502.690 ↑ 353.8 5,256 1

Gather Merge (cost=694,187.74..948,346.31 rows=1,859,586 width=137) (actual time=200,350.136..216,502.690 rows=5,256 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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, (PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.mvt_present)::integer)), (PARTIAL count(*))
  • Workers Planned: 2
  • Workers Launched: 2
3. 9,538.572 647,933.814 ↑ 530.7 1,752 3

Partial GroupAggregate (cost=693,187.71..732,703.92 rows=929,793 width=137) (actual time=200,024.341..215,977.938 rows=1,752 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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, PARTIAL sum((t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production
  • Worker 0: actual time=200312.484..216291.376 rows=1752 loops=1
  • Worker 1: actual time=199413.101..215285.514 rows=1747 loops=1
4. 207,243.792 638,395.242 ↓ 7.0 6,503,277 3

Sort (cost=693,187.71..695,512.20 rows=929,793 width=125) (actual time=200,023.073..212,798.414 rows=6,503,277 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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_dim_race_rac.rac_type_de_production
  • Sort Method: external merge Disk: 788960kB
  • Worker 0: Sort Method: external merge Disk: 788952kB
  • Worker 1: Sort Method: external merge Disk: 781240kB
  • Worker 0: actual time=200310.722..213098.476 rows=6525104 loops=1
  • Worker 1: actual time=199411.084..212118.189 rows=6459983 loops=1
5. 6,562.908 431,151.450 ↓ 7.0 6,503,277 3

Hash Join (cost=2,632.78..480,243.78 rows=929,793 width=125) (actual time=282.131..143,717.150 rows=6,503,277 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_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_present
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age)::integer = (tj_cag_age.id_age)::integer)
  • Worker 0: actual time=268.797..143630.621 rows=6525104 loops=1
  • Worker 1: actual time=268.640..143661.635 rows=6459983 loops=1
6. 7,705.317 423,886.416 ↓ 15.8 6,515,722 3

Parallel Hash Join (cost=1,662.00..467,910.98 rows=412,854 width=97) (actual time=47.846..141,295.472 rows=6,515,722 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.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_y2018m12.mvt_id_lieu_mvt)::integer = t_dim_geographique_geo.geo_id_geographique)
  • Worker 0: actual time=42.241..141203.721 rows=6538546 loops=1
  • Worker 1: actual time=41.714..141260.876 rows=6472065 loops=1
7. 4,162.686 416,143.899 ↓ 15.9 6,549,362 3

Hash Join (cost=108.19..465,273.31 rows=412,854 width=31) (actual time=35.120..138,714.633 rows=6,549,362 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, 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=29.511..138638.700 rows=6572406 loops=1
  • Worker 1: actual time=28.978..138702.496 rows=6504908 loops=1
8. 5,244.306 411,981.069 ↓ 5.2 6,549,638 3

Hash Join (cost=105.72..461,865.59 rows=1,270,320 width=29) (actual time=35.041..137,327.023 rows=6,549,638 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_race
  • Inner Unique: true
  • Hash Cond: ((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date)::text = (t_dim_date_dat.dat_id_date)::text)
  • Worker 0: actual time=29.432..137245.782 rows=6572681 loops=1
  • Worker 1: actual time=28.904..137333.338 rows=6505182 loops=1
9. 1,790.967 406,736.445 ↑ 1.3 6,549,638 3

Parallel Append (cost=0.00..439,790.50 rows=8,195,612 width=29) (actual time=34.868..135,578.815 rows=6,549,638 loops=3)

  • Worker 0: actual time=29.247..135480.519 rows=6572681 loops=1
  • Worker 1: actual time=28.677..135628.343 rows=6505182 loops=1
10. 404,945.478 404,945.478 ↑ 1.3 6,549,638 3

Parallel Seq Scan on entrepot.t_fait_bov_mouvements_mvt_y2018m12 (cost=0.00..398,812.44 rows=8,195,612 width=29) (actual time=34.867..134,981.826 rows=6,549,638 loops=3)

  • Output: t_fait_bov_mouvements_mvt_y2018m12.mvt_id_sexe, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date, t_fait_bov_mouvements_mvt_y2018m12.mvt_present, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_lieu_mvt, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_age, t_fait_bov_mouvements_mvt_y2018m12.mvt_id_race
  • Filter: (((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date)::text >= '20181201'::text) AND ((t_fait_bov_mouvements_mvt_y2018m12.mvt_id_date)::text <= '20181231'::text) AND ((t_fait_bov_mouvements_mvt_y2018m12.mvt_present)::integer = 1))
  • Rows Removed by Filter: 577240
  • Worker 0: actual time=29.245..134888.360 rows=6572681 loops=1
  • Worker 1: actual time=28.676..135032.531 rows=6505182 loops=1
11. 0.036 0.318 ↑ 1.0 31 3

Hash (cost=105.34..105.34 rows=31 width=9) (actual time=0.105..0.106 rows=31 loops=3)

  • Output: t_dim_date_dat.dat_id_date
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Worker 0: actual time=0.106..0.106 rows=31 loops=1
  • Worker 1: actual time=0.138..0.139 rows=31 loops=1
12. 0.027 0.282 ↑ 1.0 31 3

Unique (cost=104.87..105.03 rows=31 width=9) (actual time=0.083..0.094 rows=31 loops=3)

  • Output: t_dim_date_dat.dat_id_date
  • Worker 0: actual time=0.084..0.094 rows=31 loops=1
  • Worker 1: actual time=0.113..0.124 rows=31 loops=1
13. 0.120 0.255 ↑ 1.0 31 3

Sort (cost=104.87..104.95 rows=31 width=9) (actual time=0.082..0.085 rows=31 loops=3)

  • Output: t_dim_date_dat.dat_id_date
  • Sort Key: t_dim_date_dat.dat_id_date
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Worker 0: actual time=0.083..0.085 rows=31 loops=1
  • Worker 1: actual time=0.112..0.114 rows=31 loops=1
14. 0.135 0.135 ↑ 1.0 31 3

Index Scan using bse_ix_dat_jour on entrepot.t_dim_date_dat (cost=0.29..104.10 rows=31 width=9) (actual time=0.035..0.045 rows=31 loops=3)

  • Output: t_dim_date_dat.dat_id_date
  • Index Cond: (((t_dim_date_dat.dat_jour)::date >= '2018-12-01'::date) AND ((t_dim_date_dat.dat_jour)::date <= '2018-12-31'::date))
  • Worker 0: actual time=0.034..0.043 rows=31 loops=1
  • Worker 1: actual time=0.058..0.070 rows=31 loops=1
15. 0.054 0.144 ↑ 1.0 65 3

Hash (cost=1.65..1.65 rows=65 width=10) (actual time=0.047..0.048 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.049..0.049 rows=65 loops=1
  • Worker 1: actual time=0.048..0.049 rows=65 loops=1
16. 0.090 0.090 ↑ 1.0 65 3

Seq Scan on entrepot.t_dim_race_rac (cost=0.00..1.65 rows=65 width=10) (actual time=0.022..0.030 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.025..0.033 rows=65 loops=1
  • Worker 1: actual time=0.024..0.032 rows=65 loops=1
17. 18.999 37.200 ↑ 1.8 13,192 3

Parallel Hash (cost=1,262.81..1,262.81 rows=23,281 width=74) (actual time=12.400..12.400 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: 4928kB
  • Worker 0: actual time=12.631..12.631 rows=12919 loops=1
  • Worker 1: actual time=11.897..11.897 rows=13486 loops=1
18. 18.201 18.201 ↑ 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.014..6.067 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.013..6.052 rows=12919 loops=1
  • Worker 1: actual time=0.014..6.226 rows=13486 loops=1
19. 5.199 702.126 ↑ 2.3 7,501 3

Hash (cost=759.59..759.59 rows=16,895 width=36) (actual time=234.042..234.042 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=226.361..226.361 rows=7501 loops=1
  • Worker 1: actual time=226.645..226.645 rows=7501 loops=1
20. 14.739 696.927 ↑ 2.3 7,501 3

Hash Join (cost=1.60..759.59 rows=16,895 width=36) (actual time=3.568..232.309 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.121..224.644 rows=7501 loops=1
  • Worker 1: actual time=0.125..224.912 rows=7501 loops=1
21. 677.241 677.241 ↑ 1.0 43,177 3

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

  • Output: tj_cag_age.id_age, tj_cag_age.id_cag
  • Worker 0: actual time=0.023..219.544 rows=43177 loops=1
  • Worker 1: actual time=0.023..220.224 rows=43177 loops=1
22. 0.027 4.947 ↑ 1.0 9 3

Hash (cost=1.49..1.49 rows=9 width=36) (actual time=1.649..1.649 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.046..0.046 rows=9 loops=1
  • Worker 1: actual time=0.046..0.046 rows=9 loops=1
23. 4.920 4.920 ↑ 1.0 9 3

Seq Scan on entrepot.t_dim_classe_age_cag (cost=0.00..1.49 rows=9 width=36) (actual time=1.633..1.640 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.031..0.037 rows=9 loops=1