explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5BNo

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 39,425.509 ↑ 94.2 21 1

Unique (cost=301,661.04..301,720.41 rows=1,979 width=74) (actual time=39,425.500..39,425.509 rows=21 loops=1)

  • Buffers: shared hit=3038358 read=93660 dirtied=98
2. 0.051 39,425.500 ↑ 94.2 21 1

Sort (cost=301,661.04..301,665.99 rows=1,979 width=74) (actual time=39,425.499..39,425.500 rows=21 loops=1)

  • Sort Key: hopempl.nompre, e.matri, e.nompre, (max(0)), h_1.sjrsjcum, h_1.ijrscoprin, h_1.sjrscpan, h_1.ijrscoanci, h_1.ijrscofrac, h_1.sjrsrsem, h_1.sjrsrsca
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=3038358 read=93660 dirtied=98
3. 0.580 39,425.449 ↑ 94.2 21 1

GroupAggregate (cost=301,473.52..301,552.68 rows=1,979 width=74) (actual time=39,424.834..39,425.449 rows=21 loops=1)

  • Group Key: hopempl.nompre, e.matri, e.nompre, h_1.sjrsjcum, h_1.ijrscoprin, h_1.sjrscpan, h_1.ijrscoanci, h_1.ijrscofrac, h_1.sjrsrsem, h_1.sjrsrsca
  • Buffers: shared hit=3038358 read=93660 dirtied=98
4. 5.939 39,424.869 ↓ 1.1 2,100 1

Sort (cost=301,473.52..301,478.47 rows=1,979 width=70) (actual time=39,424.790..39,424.869 rows=2,100 loops=1)

  • Sort Key: hopempl.nompre, e.matri, e.nompre, h_1.sjrsjcum, h_1.ijrscoprin, h_1.sjrscpan, h_1.ijrscoanci, h_1.ijrscofrac, h_1.sjrsrsem, h_1.sjrsrsca
  • Sort Method: quicksort Memory: 392kB
  • Buffers: shared hit=3038358 read=93660 dirtied=98
5. 0.594 39,418.930 ↓ 1.1 2,100 1

Nested Loop (cost=237,122.66..301,365.17 rows=1,979 width=70) (actual time=5,648.696..39,418.930 rows=2,100 loops=1)

  • Buffers: shared hit=3038358 read=93660 dirtied=98
6. 0.905 0.905 ↑ 1.0 1 1

Index Only Scan using i_hoprols on hoprols (cost=0.42..1.54 rows=1 width=14) (actual time=0.881..0.905 rows=1 loops=1)

  • Index Cond: ((matri = '04019014'::text) AND (role = '9001'::text) AND (horsect = 'HYPFRA0940051'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=3 read=1
7. 39.958 39,417.431 ↓ 1.1 2,100 1

Hash Right Join (cost=237,122.24..301,343.83 rows=1,979 width=83) (actual time=5,647.805..39,417.431 rows=2,100 loops=1)

  • Hash Cond: ((h_1.matri)::text = (e.matri)::text)
  • Buffers: shared hit=3038355 read=93659 dirtied=98
8. 338.645 39,342.380 ↓ 1.2 66,356 1

Nested Loop (cost=232,350.21..296,338.97 rows=57,516 width=29) (actual time=1,497.846..39,342.380 rows=66,356 loops=1)

  • Buffers: shared hit=3036988 read=93644 dirtied=98
9. 136.857 1,578.951 ↓ 2.2 66,356 1

Finalize HashAggregate (cost=232,349.64..232,656.97 rows=30,733 width=12) (actual time=1,497.046..1,578.951 rows=66,356 loops=1)

  • Group Key: hophjoun.matri
  • Buffers: shared hit=2769166 read=29101
10. 64.469 1,442.094 ↓ 3.2 199,068 1

Gather (cost=225,588.38..232,042.31 rows=61,466 width=12) (actual time=1,369.993..1,442.094 rows=199,068 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2769166 read=29101
11. 494.166 1,377.625 ↓ 2.2 66,356 3 / 3

Partial HashAggregate (cost=224,588.38..224,895.71 rows=30,733 width=12) (actual time=1,363.987..1,377.625 rows=66,356 loops=3)

  • Group Key: hophjoun.matri
  • Buffers: shared hit=2769166 read=29101
12. 883.459 883.459 ↑ 1.2 2,211,867 3 / 3

Parallel Index Only Scan using i_hophjoun2 on hophjoun (cost=0.57..210,958.80 rows=2,725,917 width=12) (actual time=0.069..883.459 rows=2,211,867 loops=3)

  • Index Cond: ((dat >= '2019-08-05'::date) AND (dat <= '2019-11-12'::date))
  • Heap Fetches: 111845
  • Buffers: shared hit=2769166 read=29101
13. 37,424.784 37,424.784 ↑ 2.0 1 66,356

Index Scan using i_hophjoup on hophjoup h_1 (cost=0.57..3.56 rows=2 width=33) (actual time=0.564..0.564 rows=1 loops=66,356)

  • Index Cond: (((matri)::text = (hophjoun.matri)::text) AND (dat = (max(hophjoun.dat))))
  • Buffers: shared hit=267822 read=64543 dirtied=98
14. 0.471 35.093 ↓ 1.1 2,100 1

Hash (cost=4,747.29..4,747.29 rows=1,979 width=63) (actual time=35.093..35.093 rows=2,100 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 236kB
  • Buffers: shared hit=1367 read=15
15. 4.824 34.622 ↓ 1.1 2,100 1

Hash Right Join (cost=2,757.32..4,747.29 rows=1,979 width=63) (actual time=23.924..34.622 rows=2,100 loops=1)

  • Hash Cond: ((hopempl.matri)::text = (e.matri)::text)
  • Buffers: shared hit=1367 read=15
16. 6.927 6.927 ↑ 1.0 66,356 1

Index Only Scan using i_hopempl6 on hopempl (cost=0.42..1,721.76 rows=66,356 width=29) (actual time=0.012..6.927 rows=66,356 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=655
17. 0.410 22.871 ↓ 1.1 2,100 1

Hash (cost=2,732.17..2,732.17 rows=1,979 width=42) (actual time=22.871..22.871 rows=2,100 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 190kB
  • Buffers: shared hit=712 read=15
18. 1.027 22.461 ↓ 1.1 2,100 1

Hash Join (cost=2,551.78..2,732.17 rows=1,979 width=42) (actual time=21.640..22.461 rows=2,100 loops=1)

  • Hash Cond: ((h.matri)::text = (e.matri)::text)
  • Buffers: shared hit=712 read=15
19. 0.475 0.475 ↓ 1.1 2,100 1

Index Only Scan using i_hophjoun5 on hophjoun h (cost=0.57..175.77 rows=1,979 width=21) (actual time=0.061..0.475 rows=2,100 loops=1)

  • Index Cond: ((sectori = 'HYPFRA0940051'::text) AND (dat >= '2019-08-05'::date) AND (dat <= '2019-11-12'::date))
  • Heap Fetches: 0
  • Buffers: shared hit=57 read=15
20. 11.229 20.959 ↑ 1.0 66,356 1

Hash (cost=1,721.76..1,721.76 rows=66,356 width=29) (actual time=20.959..20.959 rows=66,356 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5107kB
  • Buffers: shared hit=655
21. 9.730 9.730 ↑ 1.0 66,356 1

Index Only Scan using i_hopempl6 on hopempl e (cost=0.42..1,721.76 rows=66,356 width=29) (actual time=0.032..9.730 rows=66,356 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=655
Planning time : 7.769 ms
Execution time : 39,429.102 ms