explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jD0

Settings
# exclusive inclusive rows x rows loops node
1. 79.468 230.350 ↓ 214.3 643 1

Hash Join (cost=3,502.17..27,935.14 rows=3 width=59) (actual time=47.631..230.35 rows=643 loops=1)

  • Buffers: shared hit=11,243
2. 122.624 122.624 ↓ 1.0 289,938 1

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

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

Hash (cost=3,502.12..3,502.12 rows=2 width=49) (actual time=28.258..28.258 rows=643 loops=1)

  • Buffers: shared hit=3,881
4. 0.443 27.946 ↓ 321.5 643 1

Nested Loop (cost=3,492.26..3,502.12 rows=2 width=49) (actual time=22.813..27.946 rows=643 loops=1)

  • Buffers: shared hit=3,881
5. 0.277 26.860 ↓ 321.5 643 1

Subquery Scan on a (cost=3,489.6..3,499.41 rows=2 width=45) (actual time=22.738..26.86 rows=643 loops=1)

  • Filter: (a.rank = 1)
  • Buffers: shared hit=3,859
6. 3.503 26.583 ↓ 9.9 2,983 1

WindowAgg (cost=3,489.6..3,495.64 rows=302 width=53) (actual time=22.737..26.583 rows=2,983 loops=1)

  • Buffers: shared hit=3,859
7. 6.706 23.080 ↓ 9.9 2,983 1

Sort (cost=3,489.6..3,490.35 rows=302 width=45) (actual time=22.723..23.08 rows=2,983 loops=1)

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

Index Only Scan using total_not_equal on data data (cost=0.42..3,477.16 rows=302 width=45) (actual time=1.107..16.374 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))
  • Heap Fetches: 2,983
  • Buffers: shared hit=3,859
9. 0.570 0.643 ↓ 7.0 7 643

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

  • Buffers: shared hit=22
10. 0.002 0.073 ↓ 7.0 7 1

Subquery Scan on b (cost=2.66..2.67 rows=1 width=4) (actual time=0.07..0.073 rows=7 loops=1)

  • Buffers: shared hit=22
11. 0.002 0.071 ↓ 7.0 7 1

Limit (cost=2.66..2.66 rows=1 width=74) (actual time=0.069..0.071 rows=7 loops=1)

  • Buffers: shared hit=22
12. 0.017 0.069 ↓ 7.0 7 1

Sort (cost=2.66..2.66 rows=1 width=74) (actual time=0.068..0.069 rows=7 loops=1)

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

Index Only Scan using total_not_equal on data data_1 (cost=0.42..2.65 rows=1 width=74) (actual time=0.037..0.052 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))
  • Heap Fetches: 18
  • Buffers: shared hit=22
Planning time : 1.065 ms
Execution time : 230.486 ms