explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vHuI

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 197.309 ↑ 16.0 1 1

Nested Loop (cost=174,873.01..301,698.62 rows=16 width=48) (actual time=197.304..197.309 rows=1 loops=1)

  • Output: CASE WHEN (((sum((t_agg_eff_dept_cag_edc.edc_present)::integer)) - (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer))) IS NULL) THEN '0'::bigint ELSE ((sum((t_agg_eff_dept_cag_edc.edc_present)::integer)) - (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer))) END, (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), CASE WHEN (((count(1)) - (count(1))) IS NULL) THEN '0'::bigint ELSE ((count(1)) - (count(1))) END, (count(1)), (sum((es.adt_entrees)::integer)), (max(batch_job_execution.end_time))
2. 0.005 144.668 ↑ 1.0 1 1

Nested Loop (cost=167,087.32..167,087.46 rows=1 width=40) (actual time=144.664..144.668 rows=1 loops=1)

  • Output: (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)), (count(1)), (sum((es.adt_entrees)::integer)), (max(batch_job_execution.end_time))
3. 0.005 144.441 ↑ 1.0 1 1

Nested Loop (cost=167,060.13..167,060.24 rows=1 width=32) (actual time=144.439..144.441 rows=1 loops=1)

  • Output: (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)), (count(1)), (sum((es.adt_entrees)::integer))
4. 0.004 58.300 ↑ 1.0 1 1

Nested Loop (cost=141,630.36..141,630.44 rows=1 width=24) (actual time=58.298..58.300 rows=1 loops=1)

  • Output: (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)), (count(1))
5. 0.005 4.920 ↑ 1.0 1 1

Nested Loop (cost=6,793.27..6,793.32 rows=1 width=16) (actual time=4.919..4.920 rows=1 loops=1)

  • Output: (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer))
6. 1.006 2.408 ↑ 1.0 1 1

Aggregate (cost=3,395.74..3,395.75 rows=1 width=8) (actual time=2.407..2.408 rows=1 loops=1)

  • Output: sum((t_agg_eff_dept_cag_edc.edc_present)::integer)
7.          

Initplan (forAggregate)

8. 0.002 0.024 ↑ 1.0 1 1

Result (cost=0.45..0.46 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)

  • Output: $0
9.          

Initplan (forResult)

10. 0.002 0.022 ↑ 1.0 1 1

Limit (cost=0.42..0.45 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)

  • Output: ((eff.edc_id_date)::integer)
11. 0.020 0.020 ↑ 139,926.0 1 1

Index Only Scan Backward using i_edc_date on entrepot.t_agg_eff_dept_cag_edc eff (cost=0.42..4,485.12 rows=139,926 width=4) (actual time=0.020..0.020 rows=1 loops=1)

  • Output: eff.edc_id_date
  • Index Cond: (eff.edc_id_date IS NOT NULL)
  • Heap Fetches: 0
12. 1.035 1.378 ↑ 1.0 7,454 1

Bitmap Heap Scan on entrepot.t_agg_eff_dept_cag_edc (cost=176.67..3,375.84 rows=7,774 width=4) (actual time=0.362..1.378 rows=7,454 loops=1)

  • Output: t_agg_eff_dept_cag_edc.edc_union_europeenne, t_agg_eff_dept_cag_edc.edc_pays, t_agg_eff_dept_cag_edc.edc_code_region, t_agg_eff_dept_cag_edc.edc_libelle_region, t_agg_eff_dept_cag_edc.edc_code_departement, t_agg_eff_dept_cag_edc.edc_libelle_departement, t_agg_eff_dept_cag_edc.edc_classe_age, t_agg_eff_dept_cag_edc.edc_cat_raciale, t_agg_eff_dept_cag_edc.edc_cat_raciale_dominante, t_agg_eff_dept_cag_edc.edc_id_sexe, t_agg_eff_dept_cag_edc.edc_id_date, t_agg_eff_dept_cag_edc.edc_present, t_agg_eff_dept_cag_edc.edc_count
  • Recheck Cond: ((t_agg_eff_dept_cag_edc.edc_id_date)::integer = $1)
  • Heap Blocks: exact=135
13. 0.343 0.343 ↑ 1.0 7,454 1

Bitmap Index Scan on i_edc_date (cost=0.00..174.72 rows=7,774 width=0) (actual time=0.343..0.343 rows=7,454 loops=1)

  • Index Cond: ((t_agg_eff_dept_cag_edc.edc_id_date)::integer = $1)
14. 1.079 2.507 ↑ 1.0 1 1

Aggregate (cost=3,397.53..3,397.54 rows=1 width=8) (actual time=2.507..2.507 rows=1 loops=1)

  • Output: sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)
15. 1.097 1.428 ↑ 1.0 7,738 1

Bitmap Heap Scan on entrepot.t_agg_eff_dept_cag_edc t_agg_eff_dept_cag_edc_1 (cost=177.44..3,377.85 rows=7,873 width=4) (actual time=0.348..1.428 rows=7,738 loops=1)

  • Output: t_agg_eff_dept_cag_edc_1.edc_union_europeenne, t_agg_eff_dept_cag_edc_1.edc_pays, t_agg_eff_dept_cag_edc_1.edc_code_region, t_agg_eff_dept_cag_edc_1.edc_libelle_region, t_agg_eff_dept_cag_edc_1.edc_code_departement, t_agg_eff_dept_cag_edc_1.edc_libelle_departement, t_agg_eff_dept_cag_edc_1.edc_classe_age, t_agg_eff_dept_cag_edc_1.edc_cat_raciale, t_agg_eff_dept_cag_edc_1.edc_cat_raciale_dominante, t_agg_eff_dept_cag_edc_1.edc_id_sexe, t_agg_eff_dept_cag_edc_1.edc_id_date, t_agg_eff_dept_cag_edc_1.edc_present, t_agg_eff_dept_cag_edc_1.edc_count
  • Recheck Cond: ((t_agg_eff_dept_cag_edc_1.edc_id_date)::integer = 20180930)
  • Heap Blocks: exact=140
16. 0.331 0.331 ↑ 1.0 7,738 1

Bitmap Index Scan on i_edc_date (cost=0.00..175.47 rows=7,873 width=0) (actual time=0.331..0.331 rows=7,738 loops=1)

  • Index Cond: ((t_agg_eff_dept_cag_edc_1.edc_id_date)::integer = 20180930)
17. 0.000 53.376 ↑ 1.0 1 1

Finalize Aggregate (cost=134,837.08..134,837.09 rows=1 width=8) (actual time=53.376..53.376 rows=1 loops=1)

  • Output: count(1)
18. 4.312 53.636 ↓ 1.5 3 1

Gather (cost=134,836.87..134,837.08 rows=2 width=8) (actual time=53.240..53.636 rows=3 loops=1)

  • Output: (PARTIAL count(1))
  • Workers Planned: 2
  • Workers Launched: 2
19. 3.776 49.324 ↑ 1.0 1 3

Partial Aggregate (cost=133,836.87..133,836.88 rows=1 width=8) (actual time=49.324..49.324 rows=1 loops=3)

  • Output: PARTIAL count(1)
  • Worker 0: actual time=47.539..47.540 rows=1 loops=1
  • Worker 1: actual time=47.772..47.772 rows=1 loops=1
20. 30.553 45.548 ↑ 1.0 60,973 3

Parallel Bitmap Heap Scan on entrepot.t_fait_tex_exploitations_fex (cost=7,127.80..133,678.17 rows=63,480 width=0) (actual time=12.873..45.548 rows=60,973 loops=3)

  • Recheck Cond: (t_fait_tex_exploitations_fex.fex_id_date = 20180930)
  • Filter: ((t_fait_tex_exploitations_fex.fex_id_lieu_exploitation IS NOT NULL) AND ((t_fait_tex_exploitations_fex.fex_nb_bovins_presents_fin_mois)::integer <> 0))
  • Rows Removed by Filter: 40089
  • Heap Blocks: exact=2397
  • Worker 0: actual time=11.134..44.028 rows=55714 loops=1
  • Worker 1: actual time=11.360..44.295 rows=56212 loops=1
21. 14.995 14.995 ↑ 1.1 303,184 1

Bitmap Index Scan on i_fex_id_date_text (cost=0.00..7,089.71 rows=325,504 width=0) (actual time=14.995..14.995 rows=303,184 loops=1)

  • Index Cond: (t_fait_tex_exploitations_fex.fex_id_date = 20180930)
22. 0.000 86.136 ↑ 1.0 1 1

Finalize Aggregate (cost=25,429.77..25,429.78 rows=1 width=8) (actual time=86.136..86.136 rows=1 loops=1)

  • Output: sum((es.adt_entrees)::integer)
23. 4.342 86.182 ↓ 1.5 3 1

Gather (cost=25,429.56..25,429.77 rows=2 width=8) (actual time=86.021..86.182 rows=3 loops=1)

  • Output: (PARTIAL sum((es.adt_entrees)::integer))
  • Workers Planned: 2
  • Workers Launched: 2
24. 2.203 81.840 ↑ 1.0 1 3

Partial Aggregate (cost=24,429.56..24,429.57 rows=1 width=8) (actual time=81.840..81.840 rows=1 loops=3)

  • Output: PARTIAL sum((es.adt_entrees)::integer)
  • Worker 0: actual time=79.906..79.906 rows=1 loops=1
  • Worker 1: actual time=80.139..80.139 rows=1 loops=1
25. 14.741 79.637 ↓ 8.3 31,336 3

Hash Join (cost=1.31..24,420.06 rows=3,798 width=4) (actual time=7.824..79.637 rows=31,336 loops=3)

  • Output: es.adt_entrees
  • Inner Unique: true
  • Hash Cond: ((es.adt_id_cause_mvt_entree)::smallint = mov.mou_id_cause_mvt)
  • Worker 0: actual time=5.943..77.702 rows=31854 loops=1
  • Worker 1: actual time=6.177..78.026 rows=30710 loops=1
26. 64.867 64.867 ↑ 1.2 73,292 3

Parallel Seq Scan on entrepot.t_agg_es_dept_mois_adm es (cost=0.00..24,137.21 rows=91,158 width=6) (actual time=7.684..64.867 rows=73,292 loops=3)

  • Output: es.adt_union_europenne, es.adt_pays, es.adt_code_region, es.adt_libelle_region, es.adt_code_departement, es.adt_libelle_departement, es.adt_categorie_raciale, es.adt_categorie_raciale_dominante, es.adt_annee, es.adt_semestre, es.adt_trimestre, es.adt_mois, es.adt_lib_mois, es.adt_mois_sans_annee, es.adt_id_sexe, es.adt_classe_age, es.adt_id_cause_mvt_entree, es.adt_id_cause_mvt_sortie, es.adt_sorties, es.adt_entrees, es.adt_exports, es.adt_reintroductions, es.adt_count
  • Filter: ((es.adt_annee)::text = '2019'::text)
  • Rows Removed by Filter: 150913
  • Worker 0: actual time=5.746..63.033 rows=74246 loops=1
  • Worker 1: actual time=5.988..62.740 rows=71988 loops=1
27. 0.009 0.029 ↑ 1.0 1 3

Hash (cost=1.30..1.30 rows=1 width=2) (actual time=0.029..0.029 rows=1 loops=3)

  • Output: mov.mou_id_cause_mvt
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.035..0.035 rows=1 loops=1
  • Worker 1: actual time=0.034..0.035 rows=1 loops=1
28. 0.020 0.020 ↑ 1.0 1 3

Seq Scan on entrepot.t_dim_bov_mouvement_mou mov (cost=0.00..1.30 rows=1 width=2) (actual time=0.019..0.020 rows=1 loops=3)

  • Output: mov.mou_id_cause_mvt
  • Filter: ((mov.mou_cause_mouvement)::text = 'N'::text)
  • Rows Removed by Filter: 23
  • Worker 0: actual time=0.026..0.028 rows=1 loops=1
  • Worker 1: actual time=0.021..0.023 rows=1 loops=1
29. 0.045 0.222 ↑ 1.0 1 1

Aggregate (cost=27.19..27.20 rows=1 width=8) (actual time=0.222..0.222 rows=1 loops=1)

  • Output: max(batch_job_execution.end_time)
30. 0.177 0.177 ↓ 1.0 617 1

Seq Scan on entrepot.batch_job_execution (cost=0.00..25.69 rows=602 width=8) (actual time=0.009..0.177 rows=617 loops=1)

  • Output: batch_job_execution.job_execution_id, batch_job_execution.version, batch_job_execution.job_instance_id, batch_job_execution.create_time, batch_job_execution.start_time, batch_job_execution.end_time, batch_job_execution.status, batch_job_execution.exit_code, batch_job_execution.exit_message, batch_job_execution.last_updated, batch_job_execution.job_configuration_location
  • Filter: ((batch_job_execution.status)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 333
31. 0.000 52.636 ↑ 16.0 1 1

Finalize GroupAggregate (cost=7,785.69..134,610.67 rows=16 width=12) (actual time=52.635..52.636 rows=1 loops=1)

  • Output: count(1), t_fait_tex_exploitations_fex_1.fex_id_date
  • Group Key: t_fait_tex_exploitations_fex_1.fex_id_date
32.          

Initplan (forFinalize GroupAggregate)

33. 0.003 0.024 ↑ 1.0 1 1

Result (cost=0.48..0.49 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)

  • Output: $2
34.          

Initplan (forResult)

35. 0.001 0.021 ↑ 1.0 1 1

Limit (cost=0.43..0.48 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)

  • Output: t_fait_tex_exploitations_fex_2.fex_id_date
36. 0.020 0.020 ↑ 4,958,940.0 1 1

Index Only Scan Backward using i_fex_id_date_text on entrepot.t_fait_tex_exploitations_fex t_fait_tex_exploitations_fex_2 (cost=0.43..228,923.13 rows=4,958,940 width=4) (actual time=0.019..0.020 rows=1 loops=1)

  • Output: t_fait_tex_exploitations_fex_2.fex_id_date
  • Index Cond: (t_fait_tex_exploitations_fex_2.fex_id_date IS NOT NULL)
  • Heap Fetches: 0
37. 5.974 54.397 ↑ 10.7 3 1

Gather (cost=7,785.20..134,609.86 rows=32 width=12) (actual time=52.494..54.397 rows=3 loops=1)

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date, (PARTIAL count(1))
  • Workers Planned: 2
  • Params Evaluated: $3
  • Workers Launched: 2
38. 5.258 48.423 ↑ 16.0 1 3

Partial GroupAggregate (cost=6,785.20..133,606.66 rows=16 width=12) (actual time=48.423..48.423 rows=1 loops=3)

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date, PARTIAL count(1)
  • Group Key: t_fait_tex_exploitations_fex_1.fex_id_date
  • Worker 0: actual time=46.677..46.677 rows=1 loops=1
  • Worker 1: actual time=46.954..46.954 rows=1 loops=1
39. 28.974 43.165 ↑ 1.0 59,471 3

Parallel Bitmap Heap Scan on entrepot.t_fait_tex_exploitations_fex t_fait_tex_exploitations_fex_1 (cost=6,785.20..133,304.29 rows=60,444 width=4) (actual time=12.117..43.165 rows=59,471 loops=3)

  • Output: t_fait_tex_exploitations_fex_1.fex_id_typologie_exploitation, t_fait_tex_exploitations_fex_1.fex_id_taille_bovins_total, t_fait_tex_exploitations_fex_1.fex_id_taille_bovins_lait, t_fait_tex_exploitations_fex_1.fex_id_taille_bovins_viande, t_fait_tex_exploitations_fex_1.fex_id_taille_vaches, t_fait_tex_exploitations_fex_1.fex_id_taille_vaches_lait, t_fait_tex_exploitations_fex_1.fex_id_taille_vaches_viande, t_fait_tex_exploitations_fex_1.fex_id_taille_male, t_fait_tex_exploitations_fex_1.fex_id_taille_male_viande, t_fait_tex_exploitations_fex_1.fex_id_taille_male_lait, t_fait_tex_exploitations_fex_1.fex_id_taille_genisses, t_fait_tex_exploitations_fex_1.fex_id_taille_genisses_lait, t_fait_tex_exploitations_fex_1.fex_id_taille_genisses_viande, t_fait_tex_exploitations_fex_1.fex_id_date, t_fait_tex_exploitations_fex_1.fex_id_lieu_exploitation, t_fait_tex_exploitations_fex_1.fex_id_exploitation_elevage, t_fait_tex_exploitations_fex_1.fex_numero_detenteur, t_fait_tex_exploitations_fex_1.fex_nb_bovins_presents_fin_mois, t_fait_tex_exploitations_fex_1.fex_ugb_tech_bovins_present, t_fait_tex_exploitations_fex_1.fex_ugbag_bovins_presents, t_fait_tex_exploitations_fex_1.fex_ugbta_bovins_presents, t_fait_tex_exploitations_fex_1.fex_ugbpc_bovins_presents, t_fait_tex_exploitations_fex_1.fex_ugbeur_bovins_presents, t_fait_tex_exploitations_fex_1.nb_bovins_presents_sur_1_an, t_fait_tex_exploitations_fex_1.fex_nb_vaches_lait, t_fait_tex_exploitations_fex_1.fex_nb_vaches_viande, t_fait_tex_exploitations_fex_1.fex_nb_genisses_lait, t_fait_tex_exploitations_fex_1.fex_nb_genisses_viande, t_fait_tex_exploitations_fex_1.fex_nb_males_lait, t_fait_tex_exploitations_fex_1.fex_nb_males_viande, t_fait_tex_exploitations_fex_1.fex_age_moyen_entree, t_fait_tex_exploitations_fex_1.fex_age_moyen, t_fait_tex_exploitations_fex_1.fex_nb_exploitations
  • Recheck Cond: (t_fait_tex_exploitations_fex_1.fex_id_date = $3)
  • Filter: ((t_fait_tex_exploitations_fex_1.fex_id_lieu_exploitation IS NOT NULL) AND ((t_fait_tex_exploitations_fex_1.fex_nb_bovins_presents_fin_mois)::integer <> 0))
  • Rows Removed by Filter: 38800
  • Heap Blocks: exact=2285
  • Worker 0: actual time=10.442..41.772 rows=55807 loops=1
  • Worker 1: actual time=10.638..42.016 rows=55174 loops=1
40. 14.191 14.191 ↑ 1.1 294,815 1

Bitmap Index Scan on i_fex_id_date_text (cost=0.00..6,748.94 rows=309,934 width=0) (actual time=14.191..14.191 rows=294,815 loops=1)

  • Index Cond: (t_fait_tex_exploitations_fex_1.fex_id_date = $3)
Planning time : 1.265 ms
Execution time : 199.606 ms