explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fo97 : Eric Testing 04

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Join (cost=69,193.66..69,244.67 rows=2,252 width=100) (actual rows= loops=)

  • Hash Cond: (bl.productgroupkey = pg.product_group_key)
2.          

CTE rank_query

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=67,357.07..68,032.55 rows=22,516 width=110) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=67,357.07..67,413.36 rows=22,516 width=70) (actual rows= loops=)

  • Sort Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), (sum(main.units)) DESC
5. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=56,702.45..65,729.31 rows=22,516 width=70) (actual rows= loops=)

  • Group Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
6. 0.000 0.000 ↓ 0.0

Gather Merge (cost=56,702.45..64,547.22 rows=45,032 width=70) (actual rows= loops=)

  • Workers Planned: 2
7. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=55,702.43..58,349.39 rows=22,516 width=70) (actual rows= loops=)

  • Group Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
8. 0.000 0.000 ↓ 0.0

Sort (cost=55,702.43..56,130.50 rows=171,228 width=24) (actual rows= loops=)

  • Sort Key: ((date_trunc('year'::text, (main.date)::timestamp with time zone))::date), main.product_group_key
9. 0.000 0.000 ↓ 0.0

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

  • Filter: ((year >= 2010) AND (release_key = 17))
10.          

CTE beforelookup

11. 0.000 0.000 ↓ 0.0

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

  • 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
12. 0.000 0.000 ↓ 0.0

CTE Scan on rank_query (cost=0.00..731.77 rows=22,516 width=72) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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