explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vnp3

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

Nested Loop (cost=337,026.39..337,027.17 rows=16 width=48) (actual time=278.350..278.353 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.004 145.718 ↑ 1.0 1 1

Nested Loop (cost=167,087.32..167,087.46 rows=1 width=40) (actual time=145.716..145.718 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 145.484 ↑ 1.0 1 1

Nested Loop (cost=167,060.13..167,060.24 rows=1 width=32) (actual time=145.481..145.484 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.003 57.846 ↑ 1.0 1 1

Nested Loop (cost=141,630.36..141,630.44 rows=1 width=24) (actual time=57.845..57.846 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.007 5.136 ↑ 1.0 1 1

Nested Loop (cost=6,793.27..6,793.32 rows=1 width=16) (actual time=5.135..5.136 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.068 2.563 ↑ 1.0 1 1

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

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

Initplan (forAggregate)

8. 0.002 0.023 ↑ 1.0 1 1

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

  • Output: $0
9.          

Initplan (forResult)

10. 0.001 0.021 ↑ 1.0 1 1

Limit (cost=0.42..0.45 rows=1 width=4) (actual time=0.020..0.021 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.090 1.472 ↑ 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.401..1.472 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.382 0.382 ↑ 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.382..0.382 rows=7,454 loops=1)

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

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

  • Output: sum((t_agg_eff_dept_cag_edc_1.edc_present)::integer)
15. 1.126 1.448 ↑ 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.340..1.448 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.322 0.322 ↑ 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.322..0.322 rows=7,738 loops=1)

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

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

  • Output: count(1)
18. 4.077 52.988 ↓ 1.5 3 1

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

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

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

  • Output: PARTIAL count(1)
  • Worker 0: actual time=47.270..47.271 rows=1 loops=1
  • Worker 1: actual time=47.453..47.454 rows=1 loops=1
20. 30.276 45.120 ↑ 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.912..45.120 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=2396
  • Worker 0: actual time=11.297..43.711 rows=55788 loops=1
  • Worker 1: actual time=11.464..43.958 rows=56571 loops=1
21. 14.844 14.844 ↑ 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.844..14.844 rows=303,184 loops=1)

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

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

  • Output: sum((es.adt_entrees)::integer)
23. 4.221 87.694 ↓ 1.5 3 1

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

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

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

  • Output: PARTIAL sum((es.adt_entrees)::integer)
  • Worker 0: actual time=81.613..81.613 rows=1 loops=1
  • Worker 1: actual time=81.829..81.830 rows=1 loops=1
25. 14.560 81.315 ↓ 8.3 31,336 3

Hash Join (cost=1.31..24,420.06 rows=3,798 width=4) (actual time=8.861..81.315 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=7.138..79.451 rows=29830 loops=1
  • Worker 1: actual time=7.243..79.714 rows=31800 loops=1
26. 66.721 66.721 ↑ 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=8.688..66.721 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=6.858..64.698 rows=70052 loops=1
  • Worker 1: actual time=7.030..65.054 rows=74314 loops=1
27. 0.009 0.034 ↑ 1.0 1 3

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

  • Output: mov.mou_id_cause_mvt
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.045..0.046 rows=1 loops=1
  • Worker 1: actual time=0.041..0.042 rows=1 loops=1
28. 0.025 0.025 ↑ 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.023..0.025 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.032..0.034 rows=1 loops=1
  • Worker 1: actual time=0.030..0.031 rows=1 loops=1
29. 0.061 0.230 ↑ 1.0 1 1

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

  • Output: max(batch_job_execution.end_time)
30. 0.169 0.169 ↓ 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.169 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. 28.161 132.630 ↑ 16.0 1 1

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

  • Output: count(1), ex.fex_id_date
  • Group Key: ex.fex_id_date
32. 15.287 104.469 ↓ 1.2 178,414 1

Nested Loop (cost=6,785.68..169,213.75 rows=145,065 width=4) (actual time=14.786..104.469 rows=178,414 loops=1)

  • Output: ex.fex_id_date
33. 0.003 0.020 ↑ 1.0 1 1

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

  • Output: $2
34.          

Initplan (forResult)

35. 0.001 0.017 ↑ 1.0 1 1

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

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date
36. 0.016 0.016 ↑ 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_1 (cost=0.43..228,923.13 rows=4,958,940 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Output: t_fait_tex_exploitations_fex_1.fex_id_date
  • Index Cond: (t_fait_tex_exploitations_fex_1.fex_id_date IS NOT NULL)
  • Heap Fetches: 0
37. 75.229 89.162 ↓ 1.2 178,414 1

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

  • 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))
  • Rows Removed by Filter: 116401
  • Heap Blocks: exact=5959
38. 13.933 13.933 ↑ 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=13.933..13.933 rows=294,815 loops=1)

  • Index Cond: (ex.fex_id_date = ($2))
Planning time : 1.244 ms
Execution time : 278.916 ms