explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yKD3 : Eric Testing 05 - after ANALYZE

Settings
# exclusive inclusive rows x rows loops node
1. 0.390 552.738 ↑ 1.7 650 1

Hash Join (cost=77,184.10..77,209.36 rows=1,115 width=98) (actual time=551.034..552.738 rows=650 loops=1)

  • Hash Cond: (bl.product_key = p.product_id)
  • Buffers: shared hit=7293
2.          

CTE rank_query

3. 6.597 543.735 ↑ 2.1 5,418 1

WindowAgg (cost=76,443.54..76,694.51 rows=11,154 width=108) (actual time=535.921..543.735 rows=5,418 loops=1)

  • Buffers: shared hit=7290
4. 7.149 537.138 ↑ 2.1 5,418 1

Sort (cost=76,443.54..76,471.43 rows=11,154 width=68) (actual time=535.911..537.138 rows=5,418 loops=1)

  • Sort Key: main.year, (sum(main.units)) DESC
  • Sort Method: quicksort Memory: 616kB
  • Buffers: shared hit=7290
5. 98.122 529.989 ↑ 2.1 5,418 1

Finalize GroupAggregate (cost=75,136.00..75,693.70 rows=11,154 width=68) (actual time=428.352..529.989 rows=5,418 loops=1)

  • Group Key: main.year, main.product_key
  • Buffers: shared hit=7290
6. 16.767 431.867 ↑ 1.9 12,029 1

Sort (cost=75,136.00..75,191.77 rows=22,308 width=68) (actual time=428.330..431.867 rows=12,029 loops=1)

  • Sort Key: main.year, main.product_key
  • Sort Method: quicksort Memory: 2076kB
  • Buffers: shared hit=7290
7. 21.795 415.100 ↑ 1.9 12,029 1

Gather (cost=71,126.67..73,524.78 rows=22,308 width=68) (actual time=392.555..415.100 rows=12,029 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=7290
8. 222.204 393.305 ↑ 2.8 4,010 3

Partial HashAggregate (cost=70,126.67..70,293.98 rows=11,154 width=68) (actual time=386.356..393.305 rows=4,010 loops=3)

  • Group Key: main.year, main.product_key
  • Buffers: shared hit=7290
9. 139.288 171.101 ↑ 1.3 137,171 3

Parallel Bitmap Heap Scan on dfo_by_quarter main (cost=15,498.47..68,372.99 rows=175,368 width=22) (actual time=28.491..171.101 rows=137,171 loops=3)

  • Recheck Cond: ((release_key = 17) AND (year >= 2010))
  • Heap Blocks: exact=1491
  • Buffers: shared hit=7290
10. 31.813 31.813 ↑ 1.0 411,512 1

Bitmap Index Scan on pk_milly_dfo_by_quarter (cost=0.00..15,393.25 rows=420,882 width=0) (actual time=31.813..31.813 rows=411,512 loops=1)

  • Index Cond: ((release_key = 17) AND (year >= 2010))
  • Buffers: shared hit=2703
11.          

CTE beforelookup

12. 3.787 551.618 ↑ 1.7 663 1

HashAggregate (cost=446.16..476.82 rows=1,115 width=102) (actual time=550.742..551.618 rows=663 loops=1)

  • Group Key: rank_query.year, CASE WHEN (rank_query.rank_by <= 50) THEN (rank_query.product_key)::integer ELSE '-1'::integer END
  • Buffers: shared hit=7290
13. 547.831 547.831 ↑ 2.1 5,418 1

CTE Scan on rank_query (cost=0.00..278.85 rows=11,154 width=70) (actual time=535.925..547.831 rows=5,418 loops=1)

  • Buffers: shared hit=7290
14. 552.077 552.077 ↑ 1.7 663 1

CTE Scan on beforelookup bl (cost=0.00..22.30 rows=1,115 width=102) (actual time=550.745..552.077 rows=663 loops=1)

  • Buffers: shared hit=7290
15. 0.134 0.271 ↑ 1.0 434 1

Hash (cost=7.34..7.34 rows=434 width=2) (actual time=0.271..0.271 rows=434 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=3
16. 0.137 0.137 ↑ 1.0 434 1

Seq Scan on dim_dfo_product p (cost=0.00..7.34 rows=434 width=2) (actual time=0.019..0.137 rows=434 loops=1)

  • Buffers: shared hit=3