explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ORow

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 284,697.695 ↑ 33.6 13 1

Sort (cost=313,812.36..313,813.45 rows=437 width=1,684) (actual time=284,697.695..284,697.695 rows=13 loops=1)

  • Sort Key: a.num_data
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1661056 read=1023675
2. 0.005 284,697.665 ↑ 33.6 13 1

Subquery Scan on a (cost=313,768.07..313,793.19 rows=437 width=1,684) (actual time=284,697.647..284,697.665 rows=13 loops=1)

  • Buffers: shared hit=1661056 read=1023675
3. 0.037 284,697.660 ↑ 33.6 13 1

WindowAgg (cost=313,768.07..313,788.82 rows=437 width=1,753) (actual time=284,697.644..284,697.660 rows=13 loops=1)

  • Buffers: shared hit=1661056 read=1023675
4. 0.039 284,697.623 ↑ 33.6 13 1

Sort (cost=313,768.07..313,769.16 rows=437 width=165) (actual time=284,697.623..284,697.623 rows=13 loops=1)

  • Sort Key: nmc.cod_sray, (CASE WHEN (sum(v_n.mnt_net) = '0'::numeric) THEN '-9999'::double precision ELSE (((sum(CASE WHEN (art.cod_gamart = 'A'::bpchar) THEN v_n.mn
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1661056 read=1023675
5. 0.023 284,697.584 ↑ 33.6 13 1

Finalize GroupAggregate (cost=312,325.14..313,748.90 rows=437 width=165) (actual time=284,052.007..284,697.584 rows=13 loops=1)

  • Group Key: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
  • Buffers: shared hit=1661053 read=1023675
6. 0.000 284,697.561 ↑ 25.7 51 1

Gather Merge (cost=312,325.14..313,675.70 rows=1,311 width=189) (actual time=283,784.282..284,697.561 rows=51 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=5093091 read=3837465
7. 3,411.564 1,126,796.992 ↑ 33.6 13 4

Partial GroupAggregate (cost=311,325.10..312,521.62 rows=437 width=189) (actual time=280,841.131..281,699.248 rows=13 loops=4)

  • Group Key: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
  • Buffers: shared hit=5093091 read=3837465
8. 5,197.584 1,123,385.428 ↓ 35.1 1,112,196 4

Sort (cost=311,325.10..311,404.21 rows=31,645 width=72) (actual time=280,790.122..280,846.357 rows=1,112,196 loops=4)

  • Sort Key: nmc.cod_sray, nmc.lib_sray COLLATE ucs_basic
  • Sort Method: quicksort Memory: 184654kB
  • Worker 0: Sort Method: quicksort Memory: 157068kB
  • Worker 1: Sort Method: quicksort Memory: 126613kB
  • Worker 2: Sort Method: quicksort Memory: 125919kB
  • Buffers: shared hit=5093091 read=3837465
9. 5,860.872 1,118,187.844 ↓ 35.1 1,112,196 4

Hash Left Join (cost=24,092.48..308,959.68 rows=31,645 width=72) (actual time=138.597..279,546.961 rows=1,112,196 loops=4)

  • Hash Cond: ((v_n.num_bu = v_comp.num_bu) AND (mag.num_ett = v_comp.num_ett))
  • Buffers: shared hit=5092998 read=3837465
10. 5,921.588 1,112,096.048 ↓ 35.1 1,112,196 4

Hash Join (cost=19,330.29..304,025.67 rows=31,645 width=50) (actual time=80.825..278,024.012 rows=1,112,196 loops=4)

  • Hash Cond: (v_n.num_ett = mag2.num_ett)
  • Buffers: shared hit=5010191 read=3836801
11. 2,127.932 1,106,172.652 ↓ 27.5 1,112,196 4

Nested Loop (cost=19,321.72..303,547.54 rows=40,492 width=47) (actual time=80.334..276,543.163 rows=1,112,196 loops=4)

  • Buffers: shared hit=5010154 read=3836798
12. 80.704 370.940 ↓ 18.4 5,115 4

Merge Join (cost=19,321.29..19,400.19 rows=278 width=38) (actual time=68.055..92.735 rows=5,115 loops=4)

  • Merge Cond: ((art.cod_sray = nmc.cod_sray) AND (art.cod_typ = nmc.cod_typ) AND (art.cod_styp = nmc.cod_styp))
  • Buffers: shared hit=18957 read=56
13. 38.628 262.068 ↑ 1.3 5,473 4

Sort (cost=19,087.80..19,105.36 rows=7,024 width=29) (actual time=63.056..65.517 rows=5,473 loops=4)

  • Sort Key: art.cod_sray, art.cod_typ, art.cod_styp
  • Sort Method: quicksort Memory: 763kB
  • Worker 0: Sort Method: quicksort Memory: 579kB
  • Worker 1: Sort Method: quicksort Memory: 571kB
  • Worker 2: Sort Method: quicksort Memory: 567kB
  • Buffers: shared hit=16445 read=1
14. 223.440 223.440 ↑ 1.3 5,473 4

Parallel Seq Scan on dim_art art (cost=0.00..18,639.03 rows=7,024 width=29) (actual time=0.142..55.860 rows=5,473 loops=4)

  • Filter: (cod_ray = '1'::numeric)
  • Rows Removed by Filter: 130495
  • Buffers: shared hit=16445 read=1
15. 9.636 28.168 ↓ 8.9 5,242 4

Sort (cost=233.49..234.96 rows=588 width=39) (actual time=4.991..7.042 rows=5,242 loops=4)

  • Sort Key: nmc.cod_sray, nmc.cod_typ, nmc.cod_styp
  • Sort Method: quicksort Memory: 71kB
  • Worker 0: Sort Method: quicksort Memory: 71kB
  • Worker 1: Sort Method: quicksort Memory: 71kB
  • Worker 2: Sort Method: quicksort Memory: 71kB
  • Buffers: shared hit=2512 read=55
16. 18.532 18.532 ↑ 1.0 588 4

Index Scan using dim_art_nmc_index on dim_art_nmc nmc (cost=0.29..206.44 rows=588 width=39) (actual time=0.248..4.633 rows=588 loops=4)

  • Index Cond: (cod_ray = '1'::numeric)
  • Buffers: shared hit=2512 read=55
17. 1,578.560 1,103,673.780 ↑ 52.0 217 20,460

Append (cost=0.44..909.17 rows=11,294 width=22) (actual time=2.108..53.943 rows=217 loops=20,460)

  • Buffers: shared hit=4991197 read=3836742
18. 137,526.850 137,526.850 ↑ 6.2 226 2,573

Index Scan using fai_vte_art_mag_jou_ble_p0_num_art_idx1 on fai_vte_art_mag_jou_ble_p0 v_n (cost=0.44..105.64 rows=1,394 width=23) (actual time=1.732..53.450 rows=226 loops=2,573)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 271
  • Buffers: shared hit=698222 read=495476
19. 137,553.234 137,553.234 ↑ 6.8 211 2,483

Index Scan using fai_vte_art_mag_jou_ble_p1_num_art_idx1 on fai_vte_art_mag_jou_ble_p1 v_n_1 (cost=0.44..107.55 rows=1,428 width=23) (actual time=1.603..55.398 rows=211 loops=2,483)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 234
  • Buffers: shared hit=557196 read=474662
20. 138,603.711 138,603.711 ↑ 6.5 218 2,631

Index Scan using fai_vte_art_mag_jou_ble_p2_num_art_idx1 on fai_vte_art_mag_jou_ble_p2 v_n_2 (cost=0.44..106.65 rows=1,419 width=22) (actual time=3.890..52.681 rows=218 loops=2,631)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 246
  • Buffers: shared hit=650316 read=489437
21. 136,449.504 136,449.504 ↑ 7.2 194 2,556

Index Scan using fai_vte_art_mag_jou_ble_p3_num_art_idx1 on fai_vte_art_mag_jou_ble_p3 v_n_3 (cost=0.44..105.35 rows=1,391 width=22) (actual time=1.572..53.384 rows=194 loops=2,556)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 210
  • Buffers: shared hit=510798 read=463521
22. 140,699.700 140,699.700 ↑ 6.4 221 2,542

Index Scan using fai_vte_art_mag_jou_ble_p4_num_art_idx1 on fai_vte_art_mag_jou_ble_p4 v_n_4 (cost=0.44..108.02 rows=1,423 width=22) (actual time=1.393..55.350 rows=221 loops=2,542)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 241
  • Buffers: shared hit=607560 read=494419
23. 136,620.330 136,620.330 ↑ 6.3 225 2,618

Index Scan using fai_vte_art_mag_jou_ble_p5_num_art_idx1 on fai_vte_art_mag_jou_ble_p5 v_n_5 (cost=0.44..106.99 rows=1,425 width=23) (actual time=1.549..52.185 rows=225 loops=2,618)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 258
  • Buffers: shared hit=681671 read=500681
24. 137,783.940 137,783.940 ↑ 6.9 205 2,510

Index Scan using fai_vte_art_mag_jou_ble_p6_num_art_idx1 on fai_vte_art_mag_jou_ble_p6 v_n_6 (cost=0.44..105.80 rows=1,408 width=22) (actual time=1.852..54.894 rows=205 loops=2,510)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 225
  • Buffers: shared hit=593204 read=415732
25. 136,857.951 136,857.951 ↑ 5.9 239 2,547

Index Scan using fai_vte_art_mag_jou_ble_p7_num_art_idx1 on fai_vte_art_mag_jou_ble_p7 v_n_7 (cost=0.44..106.70 rows=1,406 width=23) (actual time=3.191..53.733 rows=239 loops=2,547)

  • Index Cond: (num_art = art.num_art)
  • Filter: ((dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone) AND (num_bu = '6'::numeric))
  • Rows Removed by Filter: 267
  • Buffers: shared hit=692230 read=502814
26. 0.056 1.808 ↓ 1.2 62 4

Hash (cost=7.93..7.93 rows=51 width=13) (actual time=0.452..0.452 rows=62 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=13 read=3
27. 0.124 1.752 ↓ 1.2 62 4

Hash Join (cost=3.56..7.93 rows=51 width=13) (actual time=0.384..0.438 rows=62 loops=4)

  • Hash Cond: (mag.num_ett = mag2.num_ett_rgrp)
  • Buffers: shared hit=13 read=3
28. 0.132 1.084 ↓ 1.2 65 4

Hash Join (cost=1.16..4.82 rows=56 width=5) (actual time=0.234..0.271 rows=65 loops=4)

  • Hash Cond: (mag.num_reg = reg.num_reg)
  • Buffers: shared hit=10 read=2
29. 0.860 0.860 ↑ 1.0 65 4

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

  • Filter: (top_rejethi = '0'::numeric)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=6 read=2
30. 0.040 0.092 ↓ 1.2 7 4

Hash (cost=1.09..1.09 rows=6 width=5) (actual time=0.023..0.023 rows=7 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
31. 0.052 0.052 ↓ 1.2 7 4

Seq Scan on dim_mag_reg reg (cost=0.00..1.09 rows=6 width=5) (actual time=0.011..0.013 rows=7 loops=4)

  • Filter: (num_reg <> '90'::numeric)
  • Buffers: shared hit=4
32. 0.060 0.544 ↑ 1.0 62 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=3 read=1
33. 0.484 0.484 ↑ 1.0 62 4

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

  • Buffers: shared hit=3 read=1
34. 0.220 230.924 ↑ 1.1 61 4

Hash (cost=4,761.21..4,761.21 rows=65 width=42) (actual time=57.731..57.731 rows=61 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=82807 read=664
35. 0.068 230.704 ↑ 1.1 61 4

Subquery Scan on v_comp (cost=0.42..4,761.21 rows=65 width=42) (actual time=3.473..57.676 rows=61 loops=4)

  • Buffers: shared hit=82807 read=664
36. 60.368 230.636 ↑ 1.1 61 4

GroupAggregate (cost=0.42..4,760.56 rows=65 width=42) (actual time=3.471..57.659 rows=61 loops=4)

  • Group Key: a_1.num_bu, a_1.num_ett
  • Buffers: shared hit=82807 read=664
37. 170.268 170.268 ↓ 1.0 20,405 4

Index Scan using fai_vte_mag_jou_pkey on fai_vte_mag_jou a_1 (cost=0.42..4,607.80 rows=20,281 width=14) (actual time=0.826..42.567 rows=20,405 loops=4)

  • Index Cond: ((num_bu = '6'::numeric) AND (dat_vte >= '2017-01-02 00:00:00'::timestamp without time zone) AND (dat_vte <= '2017-12-31 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=82807 read=664
Planning time : 55.653 ms
Execution time : 284,704.351 ms