explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9D84

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 207.672 ↑ 4.2 13 1

Sort (cost=38,659.26..38,659.40 rows=55 width=1,684) (actual time=207.671..207.672 rows=13 loops=1)

  • Output: a.num_parent, a.num_data, a.lib_data, a.val_data_1, a.val_data_2, a.val_data_3, a.val_data_4, a.rang_data
  • Sort Key: a.num_data
  • Sort Method: quicksort Memory: 26kB
2. 0.073 207.657 ↑ 4.2 13 1

Subquery Scan on a (cost=38,218.27..38,657.67 rows=55 width=1,684) (actual time=206.952..207.657 rows=13 loops=1)

  • Output: a.num_parent, a.num_data, a.lib_data, a.val_data_1, a.val_data_2, a.val_data_3, a.val_data_4, a.rang_data
  • Filter: (a.num_filtregeo = '25'::numeric)
  • Rows Removed by Filter: 793
3. 0.640 207.584 ↑ 13.6 806 1

WindowAgg (cost=38,218.27..38,520.36 rows=10,985 width=1,708) (actual time=206.927..207.584 rows=806 loops=1)

  • Output: mag.num_ett, '0'::character varying(255), (dim_art_nmc.cod_ray)::character varying(255), (dim_art_nmc.lib_ray)::character varying(255), (sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END)), (sum(CASE WHEN (GREATEST(mag2.top_se, COALESCE(v_comp.top_se, '1'::numeric)) = '1'::numeric) THEN v_n.mnt_mrg_obj ELSE 0.0 END)), 1
4. 1.042 206.944 ↑ 13.6 806 1

Sort (cost=38,218.27..38,245.73 rows=10,985 width=88) (actual time=206.911..206.944 rows=806 loops=1)

  • Output: mag.num_ett, (sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END)), dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, (sum(CASE WHEN (GREATEST(mag2.top_se, COALESCE(v_comp.top_se, '1'::numeric)) = '1'::numeric) THEN v_n.mnt_mrg_obj ELSE 0.0 END))
  • Sort Key: dim_art_nmc.cod_ray, (sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END)) DESC
  • Sort Method: quicksort Memory: 91kB
5. 2.862 205.902 ↑ 13.6 806 1

Finalize HashAggregate (cost=37,316.22..37,481.00 rows=10,985 width=88) (actual time=205.525..205.902 rows=806 loops=1)

  • Output: mag.num_ett, sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END), dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, sum(CASE WHEN (GREATEST(mag2.top_se, COALESCE(v_comp.top_se, '1'::numeric)) = '1'::numeric) THEN v_n.mnt_mrg_obj ELSE 0.0 END)
  • Group Key: mag.num_ett, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray
6. 5.964 203.040 ↑ 10.3 3,206 1

Gather (cost=33,279.23..36,739.51 rows=32,955 width=88) (actual time=200.901..203.040 rows=3,206 loops=1)

  • Output: mag.num_ett, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, (PARTIAL sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END)), (PARTIAL sum(CASE WHEN (GREATEST(mag2.top_se, COALESCE(v_comp.top_se, '1'::numeric)) = '1'::numeric) THEN v_n.mnt_mrg_obj ELSE 0.0 END))
  • Workers Planned: 3
  • Workers Launched: 3
7. 37.379 197.076 ↑ 13.7 802 4

Partial HashAggregate (cost=32,279.23..32,444.01 rows=10,985 width=88) (actual time=196.634..197.076 rows=802 loops=4)

  • Output: mag.num_ett, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, PARTIAL sum(CASE WHEN (v_n.mnt_mrg_obj IS NULL) THEN '0'::numeric ELSE v_n.mnt_mrg_obj END), PARTIAL sum(CASE WHEN (GREATEST(mag2.top_se, COALESCE(v_comp.top_se, '1'::numeric)) = '1'::numeric) THEN v_n.mnt_mrg_obj ELSE 0.0 END)
  • Group Key: mag.num_ett, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray
  • Worker 0: actual time=195.273..195.694 rows=799 loops=1
  • Worker 1: actual time=195.260..195.690 rows=801 loops=1
  • Worker 2: actual time=195.849..196.285 rows=800 loops=1
8. 20.476 159.697 ↑ 1.2 64,792 4

Hash Left Join (cost=5,045.30..30,974.10 rows=74,579 width=66) (actual time=40.923..159.697 rows=64,792 loops=4)

  • Output: mag.num_ett, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, v_n.mnt_mrg_obj, mag2.top_se, v_comp.top_se
  • Inner Unique: true
  • Hash Cond: ((v_n.num_bu = v_comp.num_bu) AND (mag.num_ett = v_comp.num_ett))
  • Worker 0: actual time=45.039..160.930 rows=61315 loops=1
  • Worker 1: actual time=40.936..159.167 rows=62059 loops=1
  • Worker 2: actual time=45.617..161.132 rows=61889 loops=1
9. 16.177 102.677 ↑ 1.2 64,792 4

Hash Join (cost=312.33..25,836.15 rows=74,579 width=39) (actual time=4.361..102.677 rows=64,792 loops=4)

  • Output: v_n.mnt_mrg_obj, v_n.num_bu, dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, mag2.top_se, mag.num_ett
  • Hash Cond: (v_n.num_ray = dim_art_nmc.cod_ray)
  • Worker 0: actual time=4.522..101.457 rows=61315 loops=1
  • Worker 1: actual time=4.367..102.757 rows=62059 loops=1
  • Worker 2: actual time=4.856..101.093 rows=61889 loops=1
10. 29.508 82.334 ↑ 1.2 64,792 4

Hash Join (cost=8.79..24,507.15 rows=74,579 width=25) (actual time=0.183..82.334 rows=64,792 loops=4)

  • Output: v_n.mnt_mrg_obj, v_n.num_ray, v_n.num_bu, mag2.top_se, mag.num_ett
  • Hash Cond: (v_n.num_ett = mag2.num_ett)
  • Worker 0: actual time=0.215..82.204 rows=61315 loops=1
  • Worker 1: actual time=0.210..82.576 rows=62059 loops=1
  • Worker 2: actual time=0.200..81.267 rows=61889 loops=1
11. 52.674 52.674 ↑ 1.3 64,886 4

Parallel Seq Scan on dtm.fai_vte_mag_ray_jou v_n (cost=0.00..23,449.17 rows=81,796 width=22) (actual time=0.019..52.674 rows=64,886 loops=4)

  • Output: v_n.num_ett, v_n.num_bu, v_n.num_ray, v_n.num_cen, v_n.dat_vte, v_n.num_an, v_n.num_sem, v_n.num_moi, v_n.qte_vte, v_n.qte_vte_mrqln, v_n.qte_vte_prmprx, v_n.qte_vte_hyper100, v_n.qte_vte_nouveaute, v_n.mnt_net, v_n.mnt_ht, v_n.mnt_mrg, v_n.mnt_remgre, v_n.mnt_net_cm, v_n.nbr_pascai, v_n.nbr_cde_solde, v_
  • Filter: ((v_n.dat_vte >= '2018-01-02 00:00:00'::timestamp without time zone) AND (v_n.dat_vte <= '2018-12-31 00:00:00'::timestamp without time zone) AND (v_n.num_bu = '6'::numeric))
  • Rows Removed by Filter: 72672
  • Worker 0: actual time=0.023..54.749 rows=61426 loops=1
  • Worker 1: actual time=0.024..53.644 rows=62136 loops=1
  • Worker 2: actual time=0.023..53.492 rows=61976 loops=1
12. 0.013 0.152 ↓ 1.1 62 4

Hash (cost=8.05..8.05 rows=59 width=13) (actual time=0.152..0.152 rows=62 loops=4)

  • Output: mag2.top_se, mag2.num_ett, mag.num_ett
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Worker 0: actual time=0.177..0.177 rows=62 loops=1
  • Worker 1: actual time=0.171..0.171 rows=62 loops=1
  • Worker 2: actual time=0.163..0.163 rows=62 loops=1
13. 0.026 0.139 ↓ 1.1 62 4

Hash Join (cost=3.55..8.05 rows=59 width=13) (actual time=0.087..0.139 rows=62 loops=4)

  • Output: mag2.top_se, mag2.num_ett, mag.num_ett
  • Hash Cond: (mag.num_reg = reg.num_reg)
  • Worker 0: actual time=0.105..0.163 rows=62 loops=1
  • Worker 1: actual time=0.103..0.157 rows=62 loops=1
  • Worker 2: actual time=0.098..0.150 rows=62 loops=1
14. 0.029 0.092 ↓ 1.1 62 4

Hash Join (cost=2.40..6.08 rows=59 width=18) (actual time=0.054..0.092 rows=62 loops=4)

  • Output: mag2.top_se, mag2.num_ett, mag.num_ett, mag.num_reg
  • Hash Cond: (mag.num_ett = mag2.num_ett_rgrp)
  • Worker 0: actual time=0.064..0.107 rows=62 loops=1
  • Worker 1: actual time=0.062..0.102 rows=62 loops=1
  • Worker 2: actual time=0.056..0.095 rows=62 loops=1
15. 0.031 0.031 ↑ 1.0 65 4

Seq Scan on dtm.dim_mag mag (cost=0.00..2.85 rows=65 width=10) (actual time=0.010..0.031 rows=65 loops=4)

  • Output: mag.num_bu, mag.num_ett, mag.num_reg, mag.lib_mag, mag.lib_magcrt, mag.lib_adr001, mag.cod_pst, mag.lib_vil, mag.top_rejethi, mag.dat_ouv, mag.dat_ferm, mag.dat_maj, mag.tec_dat_cre, mag.tec_dat_maj, mag.tec_util_maj, mag.num_ordregmag, mag.top_web, mag.top_frch
  • Filter: (mag.top_rejethi = '0'::numeric)
  • Rows Removed by Filter: 3
  • Worker 0: actual time=0.012..0.037 rows=65 loops=1
  • Worker 1: actual time=0.013..0.035 rows=65 loops=1
  • Worker 2: actual time=0.012..0.034 rows=65 loops=1
16. 0.014 0.032 ↑ 1.0 62 4

Hash (cost=1.62..1.62 rows=62 width=13) (actual time=0.032..0.032 rows=62 loops=4)

  • Output: mag2.top_se, mag2.num_ett, mag2.num_ett_rgrp
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Worker 0: actual time=0.036..0.036 rows=62 loops=1
  • Worker 1: actual time=0.036..0.037 rows=62 loops=1
  • Worker 2: actual time=0.032..0.033 rows=62 loops=1
17. 0.018 0.018 ↑ 1.0 62 4

Seq Scan on dtm.dim_mag_trans mag2 (cost=0.00..1.62 rows=62 width=13) (actual time=0.010..0.018 rows=62 loops=4)

  • Output: mag2.top_se, mag2.num_ett, mag2.num_ett_rgrp
  • Worker 0: actual time=0.012..0.020 rows=62 loops=1
  • Worker 1: actual time=0.013..0.022 rows=62 loops=1
  • Worker 2: actual time=0.011..0.019 rows=62 loops=1
18. 0.007 0.021 ↑ 1.0 7 4

Hash (cost=1.07..1.07 rows=7 width=5) (actual time=0.021..0.021 rows=7 loops=4)

  • Output: reg.num_reg
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=0.025..0.026 rows=7 loops=1
  • Worker 1: actual time=0.026..0.026 rows=7 loops=1
  • Worker 2: actual time=0.025..0.025 rows=7 loops=1
19. 0.014 0.014 ↑ 1.0 7 4

Seq Scan on dtm.dim_mag_reg reg (cost=0.00..1.07 rows=7 width=5) (actual time=0.013..0.014 rows=7 loops=4)

  • Output: reg.num_reg
  • Worker 0: actual time=0.016..0.017 rows=7 loops=1
  • Worker 1: actual time=0.018..0.019 rows=7 loops=1
  • Worker 2: actual time=0.016..0.017 rows=7 loops=1
20. 0.007 4.166 ↑ 13.0 13 4

Hash (cost=301.43..301.43 rows=169 width=19) (actual time=4.166..4.166 rows=13 loops=4)

  • Output: dim_art_nmc.cod_ray, dim_art_nmc.lib_ray
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=4.292..4.292 rows=13 loops=1
  • Worker 1: actual time=4.144..4.144 rows=13 loops=1
  • Worker 2: actual time=4.642..4.642 rows=13 loops=1
21. 3.215 4.159 ↑ 13.0 13 4

HashAggregate (cost=298.05..299.74 rows=169 width=19) (actual time=4.156..4.159 rows=13 loops=4)

  • Output: dim_art_nmc.cod_ray, dim_art_nmc.lib_ray
  • Group Key: dim_art_nmc.cod_ray, dim_art_nmc.lib_ray
  • Worker 0: actual time=4.283..4.285 rows=13 loops=1
  • Worker 1: actual time=4.135..4.137 rows=13 loops=1
  • Worker 2: actual time=4.631..4.634 rows=13 loops=1
22. 0.944 0.944 ↑ 1.0 8,670 4

Seq Scan on dtm.dim_art_nmc (cost=0.00..254.70 rows=8,670 width=19) (actual time=0.011..0.944 rows=8,670 loops=4)

  • Output: dim_art_nmc.num_cen, dim_art_nmc.cod_ray, dim_art_nmc.cod_sray, dim_art_nmc.cod_typ, dim_art_nmc.cod_styp, dim_art_nmc.lib_typ, dim_art_nmc.lib_styp, dim_art_nmc.lib_sray, dim_art_nmc.lib_ray, dim_art_nmc.tec_dat_cre, dim_art_nmc.tec_dat_maj, dim_art_nmc.tec_util_maj
  • Worker 0: actual time=0.014..1.043 rows=8670 loops=1
  • Worker 1: actual time=0.015..1.050 rows=8670 loops=1
  • Worker 2: actual time=0.013..1.118 rows=8670 loops=1
23. 0.029 36.544 ↑ 1.0 64 4

Hash (cost=4,732.00..4,732.00 rows=65 width=42) (actual time=36.544..36.544 rows=64 loops=4)

  • Output: v_comp.top_se, v_comp.num_bu, v_comp.num_ett
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Worker 0: actual time=40.498..40.498 rows=64 loops=1
  • Worker 1: actual time=36.547..36.547 rows=64 loops=1
  • Worker 2: actual time=40.741..40.741 rows=64 loops=1
24. 0.010 36.515 ↑ 1.0 64 4

Subquery Scan on v_comp (cost=0.42..4,732.00 rows=65 width=42) (actual time=1.801..36.515 rows=64 loops=4)

  • Output: v_comp.top_se, v_comp.num_bu, v_comp.num_ett
  • Worker 0: actual time=2.119..40.469 rows=64 loops=1
  • Worker 1: actual time=2.172..36.519 rows=64 loops=1
  • Worker 2: actual time=2.174..40.713 rows=64 loops=1
25. 10.584 36.505 ↑ 1.0 64 4

GroupAggregate (cost=0.42..4,731.35 rows=65 width=42) (actual time=1.800..36.505 rows=64 loops=4)

  • Output: a_1.num_bu, a_1.num_ett, min(COALESCE(a_1.top_se, '1'::numeric))
  • Group Key: a_1.num_bu, a_1.num_ett
  • Worker 0: actual time=2.118..40.460 rows=64 loops=1
  • Worker 1: actual time=2.171..36.509 rows=64 loops=1
  • Worker 2: actual time=2.172..40.703 rows=64 loops=1
26. 25.921 25.921 ↓ 1.0 19,996 4

Index Scan using fai_vte_mag_jou_pkey on dtm.fai_vte_mag_jou a_1 (cost=0.42..4,584.12 rows=19,544 width=14) (actual time=0.193..25.921 rows=19,996 loops=4)

  • Output: a_1.num_bu, a_1.num_ett, a_1.num_cen, a_1.dat_vte, a_1.num_an, a_1.num_sem, a_1.num_moi, a_1.qte_vte, a_1.mnt_net, a_1.qte_vte_mrqln, a_1.qte_vte_prmprx, a_1.qte_vte_hyper100, a_1.qte_vte_nouveaute, a_1.mnt_ht, a_1.mnt_mrg, a_1.mnt_remgre, a_1.mnt_net_cm, a_1.nbr_pascai, a_1.mnt_netmrqln, a_1.mnt_ne
  • Index Cond: ((a_1.num_bu = '6'::numeric) AND (a_1.dat_vte >= '2018-01-02 00:00:00'::timestamp without time zone) AND (a_1.dat_vte <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Worker 0: actual time=0.212..30.281 rows=19996 loops=1
  • Worker 1: actual time=0.206..26.027 rows=19996 loops=1
  • Worker 2: actual time=0.217..30.349 rows=19996 loops=1
Planning time : 0.781 ms
Execution time : 208.934 ms