explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p8NN

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 3,253.760 ↑ 5.0 13 1

Sort (cost=117,794.39..117,794.55 rows=65 width=1,684) (actual time=3,253.760..3,253.760 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.068 3,253.746 ↑ 5.0 13 1

Subquery Scan on a (cost=117,174.93..117,792.43 rows=65 width=1,684) (actual time=3,253.042..3,253.746 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: 727
3. 0.639 3,253.678 ↑ 17.6 740 1

WindowAgg (cost=117,174.93..117,629.93 rows=13,000 width=1,750) (actual time=3,253.022..3,253.678 rows=740 loops=1)

  • Output: mag.num_ett, (nmc.cod_ray)::character varying(255), (nmc.cod_sray)::character varying(255), (nmc.lib_sray)::character varying(255), (sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '0'::numeric ELSE v_n.nbr_pascai END)), (sum(CASE WHEN (COALESCE(v_comp.top_se, '1'::numeric) = '1'::numeric) THEN v_n.nbr_pascai ELSE 0.0 END)), 1.0, 1.0, dense_rank() OVER (?)
4. 0.952 3,253.039 ↑ 17.6 740 1

Sort (cost=117,174.93..117,207.43 rows=13,000 width=130) (actual time=3,253.009..3,253.039 rows=740 loops=1)

  • Output: mag.num_ett, nmc.cod_ray, nmc.cod_sray, nmc.lib_sray, (sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '-9999'::numeric ELSE v_n.nbr_pascai END)), (sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '0'::numeric ELSE v_n.nbr_pascai END)), (sum(CASE WHEN (COALESCE(v_comp.top_se, '1'::numeric) = '1'::numeric) THEN v_n.nbr_pascai ELSE 0.0 END))
  • Sort Key: nmc.cod_sray, (sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '-9999'::numeric ELSE v_n.nbr_pascai END)) DESC
  • Sort Method: quicksort Memory: 118kB
5. 179.402 3,252.087 ↑ 17.6 740 1

HashAggregate (cost=116,059.13..116,286.63 rows=13,000 width=130) (actual time=3,251.661..3,252.087 rows=740 loops=1)

  • Output: mag.num_ett, nmc.cod_ray, nmc.cod_sray, nmc.lib_sray, sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '-9999'::numeric ELSE v_n.nbr_pascai END), sum(CASE WHEN (v_n.nbr_pascai IS NULL) THEN '0'::numeric ELSE v_n.nbr_pascai END), sum(CASE WHEN (COALESCE(v_comp.top_se, '1'::numeric) = '1'::numeric) THEN v_n.nbr_pascai ELSE 0.0 END)
  • Group Key: mag.num_ett, nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
6. 71.413 3,072.685 ↓ 1.1 251,577 1

Hash Join (cost=4,923.10..111,591.95 rows=223,359 width=70) (actual time=32.901..3,072.685 rows=251,577 loops=1)

  • Output: mag.num_ett, nmc.cod_ray, nmc.cod_sray, nmc.lib_sray, v_n.nbr_pascai, v_comp.top_se
  • Hash Cond: (v_n.num_sray = nmc.cod_sray)
7. 105.364 3,000.196 ↓ 2.4 251,577 1

Hash Left Join (cost=4,696.57..103,165.81 rows=103,757 width=51) (actual time=31.820..3,000.196 rows=251,577 loops=1)

  • Output: v_n.nbr_pascai, v_n.num_ray, v_n.num_sray, mag.num_ett, v_comp.top_se
  • Inner Unique: true
  • Hash Cond: ((v_n.num_bu = v_comp.num_bu) AND (v_n.num_ett = v_comp.num_ett))
8. 101.918 2,869.812 ↓ 2.4 251,577 1

Nested Loop (cost=1.72..97,916.15 rows=103,757 width=29) (actual time=6.790..2,869.812 rows=251,577 loops=1)

  • Output: v_n.nbr_pascai, v_n.num_ray, v_n.num_sray, v_n.num_ett, v_n.num_bu, mag.num_ett
9. 0.087 0.194 ↑ 1.0 65 1

Hash Join (cost=1.16..4.90 rows=65 width=5) (actual time=0.015..0.194 rows=65 loops=1)

  • Output: mag.num_ett
  • Hash Cond: (mag.num_reg = reg.num_reg)
10. 0.102 0.102 ↑ 1.0 65 1

Seq Scan on dtm.dim_mag mag (cost=0.00..2.85 rows=65 width=10) (actual time=0.005..0.102 rows=65 loops=1)

  • 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
11. 0.002 0.005 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=5) (actual time=0.005..0.005 rows=7 loops=1)

  • Output: reg.num_reg
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.003 0.003 ↑ 1.0 7 1

Seq Scan on dtm.dim_mag_reg reg (cost=0.00..1.07 rows=7 width=5) (actual time=0.002..0.003 rows=7 loops=1)

  • Output: reg.num_reg
13. 2,767.700 2,767.700 ↓ 2.4 3,870 65

Index Scan using fai_vte_mag_sray_jou_pkey on dtm.fai_vte_mag_sray_jou v_n (cost=0.56..1,490.37 rows=1,596 width=24) (actual time=3.759..42.580 rows=3,870 loops=65)

  • Output: v_n.num_bu, v_n.num_ett, v_n.num_ray, v_n.num_sray, 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_cde_solde, v_n.nbr_pascai,
  • Index Cond: ((v_n.num_bu = '6'::numeric) AND (v_n.num_ett = mag.num_ett) AND (v_n.num_ray = '1'::numeric) AND (v_n.dat_vte >= '2016-01-02 00:00:00'::timestamp without time zone) AND (v_n.dat_vte <= '2016-12-31 00:00:00'::timestamp without time zone))
14. 0.022 25.020 ↑ 1.2 56 1

Hash (cost=4,693.88..4,693.88 rows=65 width=42) (actual time=25.020..25.020 rows=56 loops=1)

  • Output: v_comp.top_se, v_comp.num_bu, v_comp.num_ett
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.009 24.998 ↑ 1.2 56 1

Subquery Scan on v_comp (cost=0.42..4,693.88 rows=65 width=42) (actual time=0.749..24.998 rows=56 loops=1)

  • Output: v_comp.top_se, v_comp.num_bu, v_comp.num_ett
16. 9.499 24.989 ↑ 1.2 56 1

GroupAggregate (cost=0.42..4,693.23 rows=65 width=42) (actual time=0.748..24.989 rows=56 loops=1)

  • 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
17. 15.490 15.490 ↑ 1.0 18,488 1

Index Scan using fai_vte_mag_jou_pkey on dtm.fai_vte_mag_jou a_1 (cost=0.42..4,553.17 rows=18,587 width=14) (actual time=0.089..15.490 rows=18,488 loops=1)

  • 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_netprmpr
  • Index Cond: ((a_1.num_bu = '6'::numeric) AND (a_1.dat_vte >= '2016-01-02 00:00:00'::timestamp without time zone) AND (a_1.dat_vte <= '2016-12-31 00:00:00'::timestamp without time zone))
18. 0.005 1.076 ↑ 33.6 13 1

Hash (cost=221.06..221.06 rows=437 width=29) (actual time=1.076..1.076 rows=13 loops=1)

  • Output: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.002 1.071 ↑ 33.6 13 1

Subquery Scan on nmc (cost=212.32..221.06 rows=437 width=29) (actual time=1.067..1.071 rows=13 loops=1)

  • Output: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
20. 0.318 1.069 ↑ 33.6 13 1

HashAggregate (cost=212.32..216.69 rows=437 width=43) (actual time=1.066..1.069 rows=13 loops=1)

  • Output: dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, dim_art_nmc.cod_sray, dim_art_nmc.lib_sray
  • Group Key: dim_art_nmc.cod_ray, dim_art_nmc.lib_ray, dim_art_nmc.cod_sray, dim_art_nmc.lib_sray
21. 0.751 0.751 ↑ 1.0 588 1

Index Scan using dim_art_nmc_index on dtm.dim_art_nmc (cost=0.29..206.44 rows=588 width=43) (actual time=0.016..0.751 rows=588 loops=1)

  • 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
  • Index Cond: (dim_art_nmc.cod_ray = '1'::numeric)
Planning time : 11.097 ms
Execution time : 3,254.502 ms