explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bnSE

Settings
# exclusive inclusive rows x rows loops node
1. 971.051 971.051 ↑ 6.7 663 1

Hash Join (cost=60,980.98..61,082.27 rows=4,472 width=98) (actual time=969.291..971.051 rows=663 loops=1)

  • Hash Cond: (bl.productgroupkey = pg.product_group_key)
  • Buffers: shared hit=903, temp read=550 written=550
2.          

CTE rank_query

3. 6.844 961.237 ↑ 8.3 5,418 1

WindowAgg (cost=58,050.46..59,056.57 rows=44,716 width=108) (actual time=953.238..961.237 rows=5,418 loops=1)

  • Buffers: shared hit=900, temp read=550 written=550
4. 8.185 954.393 ↑ 8.3 5,418 1

Sort (cost=58,050.46..58,162.25 rows=44,716 width=68) (actual time=953.227..954.393 rows=5,418 loops=1)

  • Sort Key: main.year, (sum(main.units)) DESC
  • Sort Method: quicksort Memory: 616kB
  • Buffers: shared hit=900, temp read=550 written=550
5. 66.857 946.208 ↑ 8.3 5,418 1

Finalize GroupAggregate (cost=37,648.95..52,758.98 rows=44,716 width=68) (actual time=605.058..946.208 rows=5,418 loops=1)

  • Group Key: main.year, main.product_group_key
  • Buffers: shared hit=900, temp read=550 written=550
6. 0.000 879.351 ↑ 7.6 11,711 1

Gather Merge (cost=37,648.95..50,746.76 rows=89,432 width=68) (actual time=605.017..879.351 rows=11,711 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2678, temp read=1631 written=1631
7. 444.915 2,463.057 ↑ 11.5 3,904 3

Partial GroupAggregate (cost=36,648.93..39,424.07 rows=44,716 width=68) (actual time=596.413..821.019 rows=3,904 loops=3)

  • Group Key: main.year, main.product_group_key
  • Buffers: shared hit=2678, temp read=1631 written=1631
8. 1,550.943 2,018.142 ↑ 1.2 137,171 3

Sort (cost=36,648.93..37,069.81 rows=168,352 width=22) (actual time=596.386..672.714 rows=137,171 loops=3)

  • Sort Key: main.year, main.product_group_key
  • Sort Method: external sort Disk: 4400kB
  • Buffers: shared hit=2678, temp read=1631 written=1631
9. 467.199 467.199 ↑ 1.2 137,171 3

Parallel Index Only Scan using eric_silly_index on dfo_by_quarter main (cost=0.56..18,580.53 rows=168,352 width=22) (actual time=0.022..155.733 rows=137,171 loops=3)

  • Index Cond: ((release_key = 17) AND (year >= 2010))
  • Heap Fetches: 0
  • Buffers: shared hit=2664