explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqG : Eric Testing 04

Settings
# exclusive inclusive rows x rows loops node
1. 0.407 1,457.189 ↑ 3.4 663 1

Hash Join (cost=69,193.66..69,244.67 rows=2,252 width=100) (actual time=1,455.458..1,457.189 rows=663 loops=1)

  • Hash Cond: (bl.productgroupkey = pg.product_group_key)
  • Buffers: shared hit=41 read=7938, temp read=575 written=577
2.          

CTE rank_query

3. 7.391 1,443.491 ↑ 4.2 5,418 1

WindowAgg (cost=67,357.07..68,032.55 rows=22,516 width=110) (actual time=1,434.870..1,443.491 rows=5,418 loops=1)

  • Buffers: shared hit=38 read=7938, temp read=575 written=577
4. 17.801 1,436.100 ↑ 4.2 5,418 1

Sort (cost=67,357.07..67,413.36 rows=22,516 width=70) (actual time=1,434.858..1,436.100 rows=5,418 loops=1)

  • Sort Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), (sum(main.units)) DESC
  • Sort Method: quicksort Memory: 616kB
  • Buffers: shared hit=38 read=7938, temp read=575 written=577
5. 72.352 1,418.299 ↑ 4.2 5,418 1

Finalize GroupAggregate (cost=56,702.45..65,729.31 rows=22,516 width=70) (actual time=1,009.907..1,418.299 rows=5,418 loops=1)

  • Group Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
  • Buffers: shared hit=35 read=7938, temp read=575 written=577
6. 0.000 1,345.947 ↑ 3.8 11,851 1

Gather Merge (cost=56,702.45..64,547.22 rows=45,032 width=70) (actual time=1,009.839..1,345.947 rows=11,851 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=302 read=23007, temp read=1733 written=1739
7. 546.915 3,840.873 ↑ 5.7 3,950 3

Partial GroupAggregate (cost=55,702.43..58,349.39 rows=22,516 width=70) (actual time=1,002.643..1,280.291 rows=3,950 loops=3)

  • Group Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
  • Buffers: shared hit=302 read=23007, temp read=1733 written=1739
8. 1,529.661 3,293.958 ↑ 1.2 137,171 3

Sort (cost=55,702.43..56,130.50 rows=171,228 width=24) (actual time=1,002.610..1,097.986 rows=137,171 loops=3)

  • Sort Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
  • Sort Method: external merge Disk: 4600kB
  • Buffers: shared hit=302 read=23007, temp read=1733 written=1739
9. 1,764.297 1,764.297 ↑ 1.2 137,171 3

Parallel Seq Scan on dfo_by_quarter main (cost=0.00..37,303.95 rows=171,228 width=24) (actual time=108.160..588.099 rows=137,171 loops=3)

  • Filter: ((year >= 2010) AND (release_key = 17))
  • Rows Removed by Filter: 546602
  • Buffers: shared hit=192 read=23007
10.          

CTE beforelookup

11. 4.718 1,456.030 ↑ 3.4 663 1

HashAggregate (cost=1,069.51..1,148.33 rows=2,252 width=104) (actual time=1,455.172..1,456.030 rows=663 loops=1)

  • Group Key: (date_trunc('year'::text, (rank_query.date)::timestamp with time zone))::date, CASE WHEN (rank_query.rank_by <= 50) THEN (rank_query.productgroupkey)::integer ELSE '-1'::integer END
  • Buffers: shared hit=38 read=7938, temp read=575 written=577
12. 1,451.312 1,451.312 ↑ 4.2 5,418 1

CTE Scan on rank_query (cost=0.00..731.77 rows=22,516 width=72) (actual time=1,434.882..1,451.312 rows=5,418 loops=1)

  • Buffers: shared hit=38 read=7938, temp read=575 written=577
13. 1,456.514 1,456.514 ↑ 3.4 663 1

CTE Scan on beforelookup bl (cost=0.00..45.04 rows=2,252 width=104) (actual time=1,455.175..1,456.514 rows=663 loops=1)

  • Buffers: shared hit=38 read=7938, temp read=575 written=577
14. 0.135 0.268 ↑ 1.0 435 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=3
15. 0.133 0.133 ↑ 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.133 rows=435 loops=1)

  • Buffers: shared hit=3