explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iG6o

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

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

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

CTE rank_query

3. 6.943 957.430 ↑ 8.3 5,418 1

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

  • Buffers: shared hit=905, temp read=552 written=552
4. 16.154 950.487 ↑ 8.3 5,418 1

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

  • Sort Key: main.year, (sum(main.units)) DESC
  • Sort Method: quicksort Memory: 616kB
  • Buffers: shared hit=905, temp read=552 written=552
5. 51.032 934.333 ↑ 8.3 5,418 1

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

  • Group Key: main.year, main.product_group_key
  • Buffers: shared hit=905, temp read=552 written=552
6. 0.000 883.301 ↑ 8.7 10,332 1

Gather Merge (cost=37,648.95..50,746.76 rows=89,432 width=68) (actual time=615.633..883.301 rows=10,332 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2678, temp read=1630 written=1630
7. 484.998 2,537.895 ↑ 13.0 3,444 3

Partial GroupAggregate (cost=36,648.93..39,424.07 rows=44,716 width=68) (actual time=604.902..845.965 rows=3,444 loops=3)

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

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

  • Sort Key: main.year, main.product_group_key
  • Sort Method: external sort Disk: 4416kB
  • Buffers: shared hit=2678, temp read=1630 written=1630
9. 500.727 500.727 ↑ 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.025..166.909 rows=137,171 loops=3)

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

CTE beforelookup

11. 4.382 966.330 ↑ 6.7 663 1

HashAggregate (cost=1,788.64..1,911.62 rows=4,472 width=102) (actual time=965.427..966.330 rows=663 loops=1)

  • Group Key: rank_query.year, CASE WHEN (rank_query.rank_by <= 50) THEN (rank_query.productgroupkey)::integer ELSE '-1'::integer END
  • Buffers: shared hit=905, temp read=552 written=552
12. 961.948 961.948 ↑ 8.3 5,418 1

CTE Scan on rank_query (cost=0.00..1,117.90 rows=44,716 width=70) (actual time=949.362..961.948 rows=5,418 loops=1)

  • Buffers: shared hit=905, temp read=552 written=552
13. 966.834 966.834 ↑ 6.7 663 1

CTE Scan on beforelookup bl (cost=0.00..89.44 rows=4,472 width=102) (actual time=965.430..966.834 rows=663 loops=1)

  • Buffers: shared hit=905, temp read=552 written=552
14. 0.141 0.272 ↑ 1.0 435 1

Hash (cost=7.35..7.35 rows=435 width=4) (actual time=0.272..0.272 rows=435 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=3
15. 0.131 0.131 ↑ 1.0 435 1

Seq Scan on dim_dfo_product_group pg (cost=0.00..7.35 rows=435 width=4) (actual time=0.017..0.131 rows=435 loops=1)

  • Buffers: shared hit=3