explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EG4s : Eric Testing

Settings
# exclusive inclusive rows x rows loops node
1. 0.419 750.291 ↓ 1.2 663 1

Hash Join (cost=40,926.30..40,939.04 rows=563 width=98) (actual time=748.479..750.291 rows=663 loops=1)

  • Hash Cond: (bl.productgroupkey = pg.product_group_key)
  • Buffers: shared hit=99 read=23103
2.          

CTE rank_query

3. 6.779 740.360 ↑ 1.0 5,418 1

WindowAgg (cost=40,546.21..40,672.87 rows=5,629 width=108) (actual time=732.301..740.360 rows=5,418 loops=1)

  • Buffers: shared hit=96 read=23103
4. 8.111 733.581 ↑ 1.0 5,418 1

Sort (cost=40,546.21..40,560.29 rows=5,629 width=68) (actual time=732.290..733.581 rows=5,418 loops=1)

  • Sort Key: main.year, (sum(main.units)) DESC
  • Sort Method: quicksort Memory: 616kB
  • Buffers: shared hit=96 read=23103
5. 30.091 725.470 ↑ 1.0 5,418 1

Finalize HashAggregate (cost=40,111.13..40,195.56 rows=5,629 width=68) (actual time=720.851..725.470 rows=5,418 loops=1)

  • Group Key: main.year, main.product_group_key
  • Buffers: shared hit=96 read=23103
6. 36.439 695.379 ↓ 1.1 12,263 1

Gather (cost=38,732.02..39,942.26 rows=11,258 width=68) (actual time=669.566..695.379 rows=12,263 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=96 read=23103
7. 245.117 658.940 ↑ 1.4 4,088 3

Partial HashAggregate (cost=37,732.02..37,816.46 rows=5,629 width=68) (actual time=650.654..658.940 rows=4,088 loops=3)

  • Group Key: main.year, main.product_group_key
  • Buffers: shared hit=96 read=23103
8. 413.823 413.823 ↑ 1.2 137,171 3

Parallel Seq Scan on dfo_by_quarter main (cost=0.00..36,019.74 rows=171,228 width=22) (actual time=96.467..413.823 rows=137,171 loops=3)

  • Filter: ((year >= 2010) AND (release_key = 17))
  • Rows Removed by Filter: 546602
  • Buffers: shared hit=96 read=23103
9.          

CTE beforelookup

10. 4.188 749.089 ↓ 1.2 663 1

HashAggregate (cost=225.16..240.64 rows=563 width=102) (actual time=748.177..749.089 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=96 read=23103
11. 744.901 744.901 ↑ 1.0 5,418 1

CTE Scan on rank_query (cost=0.00..140.72 rows=5,629 width=70) (actual time=732.306..744.901 rows=5,418 loops=1)

  • Buffers: shared hit=96 read=23103
12. 749.593 749.593 ↓ 1.2 663 1

CTE Scan on beforelookup bl (cost=0.00..11.26 rows=563 width=102) (actual time=748.180..749.593 rows=663 loops=1)

  • Buffers: shared hit=96 read=23103
13. 0.140 0.279 ↑ 1.0 435 1

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

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

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

  • Buffers: shared hit=3