explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VGwy

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 24,221.481 ↑ 7.8 6 1

Limit (cost=473,768.68..473,768.80 rows=47 width=92) (actual time=24,221.479..24,221.481 rows=6 loops=1)

2. 0.017 24,221.478 ↑ 7.8 6 1

Sort (cost=473,768.68..473,768.80 rows=47 width=92) (actual time=24,221.477..24,221.478 rows=6 loops=1)

  • Sort Key: ((((((sum(CASE WHEN (f.rn = 1) THEN f.stock_begin ELSE 0 END)))::double precision ^ '3'::double precision))::real / (GREATEST((sum(f.sales)), '1'::bigint))::double precision)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 9.375 24,221.461 ↑ 7.8 6 1

Merge Join (cost=212,317.16..473,767.37 rows=47 width=92) (actual time=24,115.320..24,221.461 rows=6 loops=1)

  • Merge Cond: ((f.org_key = skus.org_key) AND (f.option_key = skus.option_key))
4. 0.032 23,084.336 ↑ 14.0 6 1

GroupAggregate (cost=212,316.74..212,318.84 rows=84 width=22) (actual time=23,084.309..23,084.336 rows=6 loops=1)

  • Group Key: f.org_key, f.option_key
5. 0.016 23,084.304 ↑ 2.8 30 1

Sort (cost=212,316.74..212,316.95 rows=84 width=22) (actual time=23,084.298..23,084.304 rows=30 loops=1)

  • Sort Key: f.org_key, f.option_key
  • Sort Method: quicksort Memory: 27kB
6. 0.045 23,084.288 ↑ 2.8 30 1

Subquery Scan on f (cost=212,304.64..212,314.05 rows=84 width=22) (actual time=23,083.859..23,084.288 rows=30 loops=1)

  • Filter: (f.rn <= 5)
  • Rows Removed by Filter: 430
7. 0.380 23,084.243 ↓ 1.8 460 1

WindowAgg (cost=212,304.64..212,310.92 rows=251 width=90) (actual time=23,083.856..23,084.243 rows=460 loops=1)

8. 0.301 23,083.863 ↓ 1.8 460 1

Sort (cost=212,304.64..212,305.27 rows=251 width=22) (actual time=23,083.832..23,083.863 rows=460 loops=1)

  • Sort Key: w.option_key, w.year DESC, w.week DESC
  • Sort Method: quicksort Memory: 60kB
9. 23,083.562 23,083.562 ↓ 1.8 460 1

Index Scan using stores_options_weekly_pkey on stores_options_weekly w (cost=0.56..212,294.64 rows=251 width=22) (actual time=3,618.655..23,083.562 rows=460 loops=1)

  • Index Cond: ((org_key = 1) AND (store_key = 101))
  • Filter: ((group_key = 111) AND (collection_key = 0))
  • Rows Removed by Filter: 182993
10. 47.031 1,127.750 ↓ 2.7 59,606 1

Unique (cost=0.42..261,108.55 rows=22,456 width=484) (actual time=2.105..1,127.750 rows=59,606 loops=1)

11. 1,080.719 1,080.719 ↑ 1.6 258,418 1

Index Scan using sku_option_idx on skus (cost=0.42..259,018.97 rows=417,916 width=484) (actual time=2.101..1,080.719 rows=258,418 loops=1)

Planning time : 0.278 ms