explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qq0y : TPC-DS 03

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 1,706.058 ↑ 1.5 31 1

Limit (cost=79,162.34..79,162.46 rows=45 width=244) (actual time=1,706.035..1,706.058 rows=31 loops=1)

2. 0.057 1,706.044 ↑ 1.5 31 1

Sort (cost=79,162.34..79,162.46 rows=45 width=244) (actual time=1,706.034..1,706.044 rows=31 loops=1)

  • Sort Key: dt.d_year, (sum(store_sales.ss_sales_price)) DESC, item.i_brand_id
  • Sort Method: quicksort Memory: 29kB
3. 0.169 1,705.987 ↑ 1.5 31 1

GroupAggregate (cost=79,159.98..79,161.11 rows=45 width=244) (actual time=1,705.784..1,705.987 rows=31 loops=1)

  • Group Key: dt.d_year, item.i_brand, item.i_brand_id
4. 0.464 1,705.818 ↓ 6.4 287 1

Sort (cost=79,159.98..79,160.09 rows=45 width=226) (actual time=1,705.757..1,705.818 rows=287 loops=1)

  • Sort Key: dt.d_year, item.i_brand, item.i_brand_id
  • Sort Method: quicksort Memory: 65kB
5. 107.518 1,705.354 ↓ 6.4 287 1

Hash Join (cost=3,173.38..79,158.75 rows=45 width=226) (actual time=28.826..1,705.354 rows=287 loops=1)

  • Hash Cond: (store_sales.ss_item_sk = item.i_item_sk)
6. 846.763 1,591.238 ↓ 49.8 438,190 1

Hash Join (cost=1,881.56..77,833.47 rows=8,803 width=22) (actual time=14.375..1,591.238 rows=438,190 loops=1)

  • Hash Cond: (store_sales.ss_sold_date_sk = dt.d_date_sk)
7. 730.206 730.206 ↓ 1.6 2,880,404 1

Seq Scan on store_sales (cost=0.00..69,272.64 rows=1,757,664 width=22) (actual time=0.004..730.206 rows=2,880,404 loops=1)

8. 1.841 14.269 ↓ 31.6 6,000 1

Hash (cost=1,879.19..1,879.19 rows=190 width=8) (actual time=14.269..14.269 rows=6,000 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 299kB
9. 12.428 12.428 ↓ 31.6 6,000 1

Seq Scan on date_dim dt (cost=0.00..1,879.19 rows=190 width=8) (actual time=0.055..12.428 rows=6,000 loops=1)

  • Filter: (d_moy = 11)
  • Rows Removed by Filter: 67049
10. 0.006 6.598 ↑ 2.3 11 1

Hash (cost=1,291.50..1,291.50 rows=25 width=212) (actual time=6.598..6.598 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 6.592 6.592 ↑ 2.3 11 1

Seq Scan on item (cost=0.00..1,291.50 rows=25 width=212) (actual time=0.077..6.592 rows=11 loops=1)

  • Filter: (i_manufact_id = 816)
  • Rows Removed by Filter: 17989