explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WY6P

Settings
# exclusive inclusive rows x rows loops node
1. 3.257 8,349.128 ↓ 214.3 643 1

Nested Loop (cost=3,492.68..18,396.05 rows=3 width=59) (actual time=30.024..8,349.128 rows=643 loops=1)

  • Buffers: shared hit=1,984,321
2. 2.088 36.382 ↓ 321.5 643 1

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

  • Buffers: shared hit=3,881
3. 0.752 33.008 ↓ 321.5 643 1

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

  • Filter: (a.rank = 1)
  • Buffers: shared hit=3,859
4. 8.003 32.256 ↓ 9.9 2,983 1

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

  • Buffers: shared hit=3,859
5. 7.328 24.253 ↓ 9.9 2,983 1

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

  • Sort Key: data.code_h, data.value_ind DESC
  • Sort Method: quicksort Memory: 330kB
  • Buffers: shared hit=3,859
6. 16.925 16.925 ↓ 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.145..16.925 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
7. 1.210 1.286 ↓ 7.0 7 643

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

  • Buffers: shared hit=22
8. 0.002 0.076 ↓ 7.0 7 1

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

  • Buffers: shared hit=22
9. 0.003 0.074 ↓ 7.0 7 1

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

  • Buffers: shared hit=22
10. 0.017 0.071 ↓ 7.0 7 1

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

  • Sort Key: data_1.value_ind DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=22
11. 0.054 0.054 ↓ 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.038..0.054 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
12. 8,309.489 8,309.489 ↑ 1.0 1 643

Index Only Scan using total_equal on data tot (cost=0.42..7,446.95 rows=1 width=41) (actual time=8.514..12.923 rows=1 loops=643)

  • Index Cond: ((tot.level_h = a.level_h) AND (tot.code_h = a.code_h) AND (tot.level_w = a.level_w) AND (tot.code_w = a.code_w) AND (tot.brdwn = a.brdwn) AND (tot.value_code = a.value_code))
  • Heap Fetches: 643
  • Buffers: shared hit=1,980,440
Planning time : 1.058 ms
Execution time : 8,349.507 ms