explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PaMu : Optimization for: plan #TE84

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 79.865 241.868 ↓ 214.3 643 1

Hash Join (cost=5,935..30,367.97 rows=3 width=59) (actual time=60.089..241.868 rows=643 loops=1)

  • Buffers: shared hit=13,394
2. 121.328 121.328 ↓ 1.0 289,938 1

Seq Scan on data tot (cost=0..15,820.66 rows=287,076 width=41) (actual time=1.843..121.328 rows=289,938 loops=1)

  • Filter: (tot.brdwn_code = 'total'::text)
  • Buffers: shared hit=7,362
3. 0.316 40.675 ↓ 321.5 643 1

Hash (cost=5,934.95..5,934.95 rows=2 width=49) (actual time=40.675..40.675 rows=643 loops=1)

  • Buffers: shared hit=6,032
4. 0.504 40.359 ↓ 321.5 643 1

Nested Loop (cost=5,925.08..5,934.95 rows=2 width=49) (actual time=35.807..40.359 rows=643 loops=1)

  • Buffers: shared hit=6,032
5. 0.269 39.212 ↓ 321.5 643 1

Subquery Scan on a (cost=5,922.42..5,932.23 rows=2 width=45) (actual time=35.716..39.212 rows=643 loops=1)

  • Filter: (a.rank = 1)
  • Buffers: shared hit=6,009
6. 3.049 38.943 ↓ 9.9 2,983 1

WindowAgg (cost=5,922.42..5,928.46 rows=302 width=53) (actual time=35.715..38.943 rows=2,983 loops=1)

  • Buffers: shared hit=6,009
7. 6.915 35.894 ↓ 9.9 2,983 1

Sort (cost=5,922.42..5,923.17 rows=302 width=45) (actual time=35.701..35.894 rows=2,983 loops=1)

  • Sort Key: data.code_h, data.value_ind DESC
  • Sort Method: quicksort Memory: 330kB
  • Buffers: shared hit=6,009
8. 28.979 28.979 ↓ 9.9 2,983 1

Index Only Scan using data_main on data data (cost=0.42..5,909.98 rows=302 width=45) (actual time=2.643..28.979 rows=2,983 loops=1)

  • Index Cond: ((data.year = 2,017) AND (data.level_h = 7) AND (data.level_w = 99) AND (data.code_w = 'total'::text) AND (data.brdwn = 'prof'::text) AND (data.value_code = 'empl'::text))
  • Filter: (data.brdwn_code <> 'total'::text)
  • Heap Fetches: 4,283
  • Buffers: shared hit=6,009
9. 0.556 0.643 ↓ 7.0 7 643

Materialize (cost=2.67..2.68 rows=1 width=4) (actual time=0..0.001 rows=7 loops=643)

  • Buffers: shared hit=23
10. 0.002 0.087 ↓ 7.0 7 1

Subquery Scan on b (cost=2.67..2.68 rows=1 width=4) (actual time=0.084..0.087 rows=7 loops=1)

  • Buffers: shared hit=23
11. 0.002 0.085 ↓ 7.0 7 1

Limit (cost=2.67..2.67 rows=1 width=74) (actual time=0.083..0.085 rows=7 loops=1)

  • Buffers: shared hit=23
12. 0.019 0.083 ↓ 7.0 7 1

Sort (cost=2.67..2.67 rows=1 width=74) (actual time=0.082..0.083 rows=7 loops=1)

  • Sort Key: data_1.value_ind DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=23
13. 0.064 0.064 ↓ 18.0 18 1

Index Only Scan using data_main on data data_1 (cost=0.42..2.66 rows=1 width=74) (actual time=0.046..0.064 rows=18 loops=1)

  • Index Cond: ((data_1.year = 2,017) AND (data_1.level_h = 0) AND (data_1.code_h = 'LV'::text) AND (data_1.brdwn = 'prof'::text) AND (data_1.value_code = 'empl'::text))
  • Filter: (data_1.brdwn_code <> 'total'::text)
  • Heap Fetches: 19
  • Buffers: shared hit=23
Planning time : 0.979 ms
Execution time : 242.004 ms