explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dY8X

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop (cost=337,026.39..337,027.17 rows=16 width=48) (actual rows= loops=)

  • 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.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • Output: (sum((t_agg_eff_dept_cag_edc.edc_present)::integer)), (sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer))
6. 0.000 0.000 ↓ 0.0

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

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

Initplan (forAggregate)

8. 0.000 0.000 ↓ 0.0

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

  • Output: $0
9.          

Initplan (forResult)

10. 0.000 0.000 ↓ 0.0

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

  • Output: ((eff.edc_id_date)::integer)
11. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Output: eff.edc_id_date
  • Index Cond: (eff.edc_id_date IS NOT NULL)
12. 0.000 0.000 ↓ 0.0

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

  • 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)
13. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((t_agg_eff_dept_cag_edc.edc_id_date)::integer = $1)
14. 0.000 0.000 ↓ 0.0

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

  • Output: sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)
15. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • 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)
16. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((t_agg_eff_dept_cag_edc_1.edc_id_date)::integer = 20180930)
17. 0.000 0.000 ↓ 0.0

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

  • Output: count(1)
18. 0.000 0.000 ↓ 0.0

Gather (cost=134,836.87..134,837.08 rows=2 width=8) (actual rows= loops=)

  • Output: (PARTIAL count(1))
  • Workers Planned: 2
19. 0.000 0.000 ↓ 0.0

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

  • Output: PARTIAL count(1)
20. 0.000 0.000 ↓ 0.0

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

  • 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))
21. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (t_fait_tex_exploitations_fex.fex_id_date = 20180930)
22. 0.000 0.000 ↓ 0.0

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

  • Output: sum((es.adt_entrees)::integer)
23. 0.000 0.000 ↓ 0.0

Gather (cost=25,429.56..25,429.77 rows=2 width=8) (actual rows= loops=)

  • Output: (PARTIAL sum((es.adt_entrees)::integer))
  • Workers Planned: 2
24. 0.000 0.000 ↓ 0.0

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

  • Output: PARTIAL sum((es.adt_entrees)::integer)
25. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.31..24,420.06 rows=3,798 width=4) (actual rows= loops=)

  • Output: es.adt_entrees
  • Inner Unique: true
  • Hash Cond: ((es.adt_id_cause_mvt_entree)::smallint = mov.mou_id_cause_mvt)
26. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on entrepot.t_agg_es_dept_mois_adm es (cost=0.00..24,137.21 rows=91,158 width=6) (actual rows= loops=)

  • 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)
27. 0.000 0.000 ↓ 0.0

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

  • Output: mov.mou_id_cause_mvt
28. 0.000 0.000 ↓ 0.0

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

  • Output: mov.mou_id_cause_mvt
  • Filter: ((mov.mou_cause_mouvement)::text = 'N'::text)
29. 0.000 0.000 ↓ 0.0

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

  • Output: max(batch_job_execution.end_time)
30. 0.000 0.000 ↓ 0.0

Seq Scan on entrepot.batch_job_execution (cost=0.00..25.69 rows=602 width=8) (actual rows= loops=)

  • 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)
31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=169,939.07..169,939.23 rows=16 width=12) (actual rows= loops=)

  • Output: count(1), ex.fex_id_date
  • Group Key: ex.fex_id_date
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,785.68..169,213.75 rows=145,065 width=4) (actual rows= loops=)

  • Output: ex.fex_id_date
33. 0.000 0.000 ↓ 0.0

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

  • Output: $2
34.          

Initplan (forResult)

35. 0.000 0.000 ↓ 0.0

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

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date
36. 0.000 0.000 ↓ 0.0

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

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date
  • Index Cond: (t_fait_tex_exploitations_fex_1.fex_id_date IS NOT NULL)
37. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on entrepot.t_fait_tex_exploitations_fex ex (cost=6,785.20..167,762.60 rows=145,065 width=4) (actual rows= loops=)

  • Output: ex.fex_id_typologie_exploitation, ex.fex_id_taille_bovins_total, ex.fex_id_taille_bovins_lait, ex.fex_id_taille_bovins_viande, ex.fex_id_taille_vaches, ex.fex_id_taille_vaches_lait, ex.fex_id_taille_vaches_viande, ex.fex_id_taille_male, ex.fex_id_taille_male_viande, ex.fex_id_taille_male_lait, ex.fex_id_taille_genisses, ex.fex_id_taille_genisses_lait, ex.fex_id_taille_genisses_viande, ex.fex_id_date, ex.fex_id_lieu_exploitation, ex.fex_id_exploitation_elevage, ex.fex_numero_detenteur, ex.fex_nb_bovins_presents_fin_mois, ex.fex_ugb_tech_bovins_present, ex.fex_ugbag_bovins_presents, ex.fex_ugbta_bovins_presents, ex.fex_ugbpc_bovins_presents, ex.fex_ugbeur_bovins_presents, ex.nb_bovins_presents_sur_1_an, ex.fex_nb_vaches_lait, ex.fex_nb_vaches_viande, ex.fex_nb_genisses_lait, ex.fex_nb_genisses_viande, ex.fex_nb_males_lait, ex.fex_nb_males_viande, ex.fex_age_moyen_entree, ex.fex_age_moyen, ex.fex_nb_exploitations
  • Recheck Cond: (ex.fex_id_date = ($2))
  • Filter: ((ex.fex_id_lieu_exploitation IS NOT NULL) AND ((ex.fex_nb_bovins_presents_fin_mois)::integer <> 0))
38. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (ex.fex_id_date = ($2))