explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Hq1 : Optimization for: plan #ORow

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.019 8,897.717 ↑ 33.6 13 1

Sort (cost=313,812.36..313,813.45 rows=437 width=1,684) (actual time=8,897.716..8,897.717 rows=13 loops=1)

  • Sort Key: a.num_data
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=2648046 read=1131
2. 0.004 8,897.698 ↑ 33.6 13 1

Subquery Scan on a (cost=313,768.07..313,793.19 rows=437 width=1,684) (actual time=8,897.682..8,897.698 rows=13 loops=1)

  • Buffers: shared hit=2648046 read=1131
3. 0.024 8,897.694 ↑ 33.6 13 1

WindowAgg (cost=313,768.07..313,788.82 rows=437 width=1,753) (actual time=8,897.681..8,897.694 rows=13 loops=1)

  • Buffers: shared hit=2648046 read=1131
4. 0.024 8,897.670 ↑ 33.6 13 1

Sort (cost=313,768.07..313,769.16 rows=437 width=165) (actual time=8,897.669..8,897.670 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.mnt_net ELSE '0'::numeric END))::double precision / (sum(v_n.mnt_net))::double precision) * '100'::double precision) END) DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=2648046 read=1131
5. 0.000 8,897.646 ↑ 33.6 13 1

Finalize GroupAggregate (cost=312,325.14..313,748.90 rows=437 width=165) (actual time=8,221.714..8,897.646 rows=13 loops=1)

  • Group Key: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
  • Buffers: shared hit=2648046 read=1131
6. 0.000 9,090.358 ↑ 25.2 52 1

Gather Merge (cost=312,325.14..313,675.70 rows=1,311 width=189) (actual time=7,929.501..9,090.358 rows=52 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=8927059 read=3497
7. 3,086.928 33,583.740 ↑ 33.6 13 4

Partial GroupAggregate (cost=311,325.10..312,521.62 rows=437 width=189) (actual time=7,617.201..8,395.935 rows=13 loops=4)

  • Group Key: nmc.cod_ray, nmc.cod_sray, nmc.lib_sray
  • Buffers: shared hit=8927059 read=3497
8. 1,804.692 30,496.812 ↓ 35.1 1,112,196 4

Sort (cost=311,325.10..311,404.21 rows=31,645 width=72) (actual time=7,575.173..7,624.203 rows=1,112,196 loops=4)

  • Sort Key: nmc.cod_sray, nmc.lib_sray COLLATE ucs_basic
  • Sort Method: quicksort Memory: 189239kB
  • Worker 0: Sort Method: quicksort Memory: 128272kB
  • Worker 1: Sort Method: quicksort Memory: 124271kB
  • Worker 2: Sort Method: quicksort Memory: 152472kB
  • Buffers: shared hit=8927059 read=3497
9. 1,601.700 28,692.120 ↓ 35.1 1,112,196 4

Hash Left Join (cost=24,092.48..308,959.68 rows=31,645 width=72) (actual time=95.265..7,173.030 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=8926966 read=3497
10. 1,267.808 26,932.896 ↓ 35.1 1,112,196 4

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

  • Hash Cond: (v_n.num_ett = mag2.num_ett)
  • Buffers: shared hit=8843495 read=3497
11. 499.428 25,664.420 ↓ 27.5 1,112,196 4

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

  • Buffers: shared hit=8843455 read=3497
12. 16.772 244.712 ↓ 18.4 5,115 4

Merge Join (cost=19,321.29..19,400.19 rows=278 width=38) (actual time=55.462..61.178 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=19013
13. 32.936 218.584 ↑ 1.3 5,473 4

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

  • Sort Key: art.cod_sray, art.cod_typ, art.cod_styp
  • Sort Method: quicksort Memory: 741kB
  • Worker 0: Sort Method: quicksort Memory: 582kB
  • Worker 1: Sort Method: quicksort Memory: 569kB
  • Worker 2: Sort Method: quicksort Memory: 588kB
  • Buffers: shared hit=16446
14. 185.648 185.648 ↑ 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.020..46.412 rows=5,473 loops=4)

  • Filter: (cod_ray = '1'::numeric)
  • Rows Removed by Filter: 130495
  • Buffers: shared hit=16446
15. 2.956 9.356 ↓ 8.9 5,236 4

Sort (cost=233.49..234.96 rows=588 width=39) (actual time=1.869..2.339 rows=5,236 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=2567
16. 6.400 6.400 ↑ 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.031..1.600 rows=588 loops=4)

  • Index Cond: (cod_ray = '1'::numeric)
  • Buffers: shared hit=2567
17. 423.986 24,920.280 ↑ 52.0 217 20,460

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

  • Buffers: shared hit=8824442 read=3497
18. 3,259.991 3,259.991 ↑ 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=0.038..1.267 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=1193559 read=139
19. 2,843.035 2,843.035 ↑ 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=0.045..1.145 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=1031723 read=135
20. 3,183.510 3,183.510 ↑ 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=0.067..1.210 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=1139633 read=120
21. 2,678.688 2,678.688 ↑ 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=0.034..1.048 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=974271 read=48
22. 3,083.446 3,083.446 ↑ 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=0.037..1.213 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=1101914 read=65
23. 3,264.646 3,264.646 ↑ 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=0.036..1.247 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=1182310 read=42
24. 3,014.510 3,014.510 ↑ 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=0.044..1.201 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=1006038 read=2898
25. 3,168.468 3,168.468 ↑ 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=0.065..1.244 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=1194994 read=50
26. 0.052 0.668 ↓ 1.2 62 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=16
27. 0.112 0.616 ↓ 1.2 62 4

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

  • Hash Cond: (mag.num_ett = mag2.num_ett_rgrp)
  • Buffers: shared hit=16
28. 0.112 0.360 ↓ 1.2 65 4

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

  • Hash Cond: (mag.num_reg = reg.num_reg)
  • Buffers: shared hit=12
29. 0.164 0.164 ↑ 1.0 65 4

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

  • Filter: (top_rejethi = '0'::numeric)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=8
30. 0.036 0.084 ↓ 1.2 7 4

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

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

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

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

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

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

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

  • Buffers: shared hit=4
34. 0.128 157.524 ↑ 1.1 61 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=83471
35. 0.036 157.396 ↑ 1.1 61 4

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

  • Buffers: shared hit=83471
36. 44.364 157.360 ↑ 1.1 61 4

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

  • Group Key: a_1.num_bu, a_1.num_ett
  • Buffers: shared hit=83471
37. 112.996 112.996 ↓ 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.166..28.249 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=83471
Planning time : 2.352 ms
Execution time : 9,097.571 ms