explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bRnY

Settings
# exclusive inclusive rows x rows loops node
1. 0.396 523.519 ↑ 1.7 650 1

Hash Join (cost=76,473.53..76,498.85 rows=1,118 width=98) (actual time=521.765..523.519 rows=650 loops=1)

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

CTE rank_query

3. 6.615 514.471 ↑ 2.1 5,418 1

WindowAgg (cost=75,731.27..75,982.82 rows=11,180 width=108) (actual time=506.554..514.471 rows=5,418 loops=1)

  • Buffers: shared hit=7290
4. 9.490 507.856 ↑ 2.1 5,418 1

Sort (cost=75,731.27..75,759.22 rows=11,180 width=68) (actual time=506.543..507.856 rows=5,418 loops=1)

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

Finalize GroupAggregate (cost=74,420.49..74,979.49 rows=11,180 width=68) (actual time=476.782..498.366 rows=5,418 loops=1)

  • Group Key: main.year, main.product_key
  • Buffers: shared hit=7290
6. 11.552 479.844 ↑ 1.9 12,085 1

Sort (cost=74,420.49..74,476.39 rows=22,360 width=68) (actual time=476.760..479.844 rows=12,085 loops=1)

  • Sort Key: main.year, main.product_key
  • Sort Method: quicksort Memory: 2084kB
  • Buffers: shared hit=7290
7. 88.718 468.292 ↑ 1.9 12,085 1

Gather (cost=70,401.43..72,805.13 rows=22,360 width=68) (actual time=439.390..468.292 rows=12,085 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=7290
8. 229.917 379.574 ↑ 2.8 4,028 3

Partial HashAggregate (cost=69,401.43..69,569.13 rows=11,180 width=68) (actual time=369.822..379.574 rows=4,028 loops=3)

  • Group Key: main.year, main.product_key
  • Buffers: shared hit=7290
9. 116.852 149.657 ↑ 1.2 137,171 3

Parallel Bitmap Heap Scan on dfo_by_quarter main (cost=14,933.14..67,711.71 rows=168,972 width=22) (actual time=11.227..149.657 rows=137,171 loops=3)

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

Bitmap Index Scan on pk_milly_dfo_by_quarter (cost=0.00..14,831.75 rows=405,532 width=0) (actual time=32.805..32.805 rows=411,512 loops=1)

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

CTE beforelookup

12. 3.876 522.387 ↑ 1.7 663 1

HashAggregate (cost=447.20..477.94 rows=1,118 width=102) (actual time=521.460..522.387 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. 518.511 518.511 ↑ 2.1 5,418 1

CTE Scan on rank_query (cost=0.00..279.50 rows=11,180 width=70) (actual time=506.558..518.511 rows=5,418 loops=1)

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

CTE Scan on beforelookup bl (cost=0.00..22.36 rows=1,118 width=102) (actual time=521.462..522.842 rows=663 loops=1)

  • Buffers: shared hit=7290
15. 0.141 0.281 ↑ 1.0 434 1

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

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

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

  • Buffers: shared hit=3