explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1rYb

Settings
# exclusive inclusive rows x rows loops node
1. 1.125 1,128.188 ↓ 62.5 250 1

Sort (cost=72,087.24..72,087.25 rows=4 width=136) (actual time=1,128.174..1,128.188 rows=250 loops=1)

  • Sort Key: f.cols_count DESC, f.store, f.category
  • Sort Method: quicksort Memory: 1181kB
2.          

CTE frame

3. 0.610 1,122.626 ↑ 1.3 178 1

WindowAgg (cost=71,776.82..72,065.28 rows=239 width=192) (actual time=1,056.513..1,122.626 rows=178 loops=1)

4. 26.441 1,122.016 ↑ 1.3 178 1

GroupAggregate (cost=71,776.82..72,056.92 rows=239 width=181) (actual time=1,055.631..1,122.016 rows=178 loops=1)

  • Group Key: st.store_key, st.store_name, st.store_status, ((groups.properties ->> '1'::text))
  • Filter: ((sum((((LEAST(((sum(predictions_skus_daily.potential) / '4'::numeric)), ((GREATEST(stock_current.economical, 0)))::numeric))::double precision * ss.stock_factor) * (('1'::numeric - sr.return_perc))::double precision)) > '0'::double precision) AND (sum((GREATEST(stock_current.economical, 0))) > 0))
  • Rows Removed by Filter: 26
5. 25.131 1,061.807 ↓ 7.9 16,884 1

Sort (cost=71,776.82..71,782.19 rows=2,150 width=165) (actual time=1,054.864..1,061.807 rows=16,884 loops=1)

  • Sort Key: st.store_key, st.store_name, st.store_status, ((groups.properties ->> '1'::text))
  • Sort Method: external merge Disk: 2136kB
6. 7.840 1,036.676 ↓ 7.9 16,884 1

Hash Join (cost=57,708.72..71,657.82 rows=2,150 width=165) (actual time=873.717..1,036.676 rows=16,884 loops=1)

  • Hash Cond: (sk.group_key = groups.group_key)
7. 7.258 1,028.777 ↓ 8.0 16,884 1

Hash Left Join (cost=57,703.77..71,641.76 rows=2,106 width=78) (actual time=873.649..1,028.777 rows=16,884 loops=1)

  • Hash Cond: ((sk.org_key = sr.org_key) AND (st.store_key = sr.store_key) AND (sk.group_key = sr.group_key))
8. 71.280 1,017.482 ↓ 8.0 16,884 1

Hash Right Join (cost=57,357.95..70,610.40 rows=2,106 width=74) (actual time=869.528..1,017.482 rows=16,884 loops=1)

  • Hash Cond: ((stock_current.org_key = sk.org_key) AND (stock_current.store_key = st.store_key) AND (stock_current.sku_key = sk.sku_key))
9. 85.996 85.996 ↑ 1.0 406,239 1

Seq Scan on stock_current (cost=0.00..8,681.58 rows=406,239 width=12) (actual time=0.019..85.996 rows=406,239 loops=1)

  • Filter: (org_key = 1)
10. 6.654 860.206 ↓ 8.0 16,884 1

Hash (cost=57,321.10..57,321.10 rows=2,106 width=74) (actual time=860.206..860.206 rows=16,884 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1648kB
11. 7.118 853.552 ↓ 8.0 16,884 1

Hash Join (cost=56,299.86..57,321.10 rows=2,106 width=74) (actual time=808.198..853.552 rows=16,884 loops=1)

  • Hash Cond: ((sk.group_key = ss.group_key) AND (st.store_key = ss.store_key))
12. 15.637 838.962 ↓ 5.0 19,179 1

Hash Join (cost=55,884.82..56,886.09 rows=3,802 width=68) (actual time=800.714..838.962 rows=19,179 loops=1)

  • Hash Cond: (predictions_skus_daily.sku_key = sk.sku_key)
13. 6.240 742.957 ↓ 5.0 19,179 1

Hash Join (cost=46,266.07..46,647.36 rows=3,802 width=66) (actual time=720.166..742.957 rows=19,179 loops=1)

  • Hash Cond: (predictions_skus_daily.store_key = st.store_key)
14. 364.131 736.649 ↓ 3.0 19,179 1

HashAggregate (cost=46,254.07..46,555.01 rows=6,336 width=40) (actual time=720.084..736.649 rows=19,179 loops=1)

  • Group Key: predictions_skus_daily.org_key, predictions_skus_daily.store_key, predictions_skus_daily.sku_key
  • Filter: (count(*) > 25)
15. 372.518 372.518 ↑ 1.0 556,191 1

Index Scan using org_date_key_idx on predictions_skus_daily (cost=0.44..39,233.96 rows=561,609 width=13) (actual time=0.023..372.518 rows=556,191 loops=1)

  • Index Cond: ((org_key = 1) AND (date_key >= (now())::date) AND (date_key <= ((now())::date + '28 days'::interval)))
16. 0.027 0.068 ↑ 1.0 120 1

Hash (cost=10.50..10.50 rows=120 width=26) (actual time=0.067..0.068 rows=120 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
17. 0.041 0.041 ↑ 1.0 120 1

Seq Scan on stores st (cost=0.00..10.50 rows=120 width=26) (actual time=0.007..0.041 rows=120 loops=1)

  • Filter: (org_key = 1)
18. 40.251 80.368 ↑ 1.0 106,380 1

Hash (cost=7,768.38..7,768.38 rows=106,430 width=10) (actual time=80.368..80.368 rows=106,380 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3307kB
19. 40.117 40.117 ↑ 1.0 106,380 1

Seq Scan on skus sk (cost=0.00..7,768.38 rows=106,430 width=10) (actual time=0.010..40.117 rows=106,380 loops=1)

  • Filter: (org_key = 1)
20. 1.349 7.472 ↑ 1.0 8,009 1

Hash (cost=294.91..294.91 rows=8,009 width=12) (actual time=7.472..7.472 rows=8,009 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 409kB
21. 6.123 6.123 ↑ 1.0 8,009 1

Seq Scan on stores_settings ss (cost=0.00..294.91 rows=8,009 width=12) (actual time=0.012..6.123 rows=8,009 loops=1)

  • Filter: ((stock_factor > '0'::double precision) AND (org_key = 1))
  • Rows Removed by Filter: 1785
22. 2.077 4.037 ↑ 1.0 9,794 1

Hash (cost=174.43..174.43 rows=9,794 width=10) (actual time=4.037..4.037 rows=9,794 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 538kB
23. 1.960 1.960 ↑ 1.0 9,794 1

Seq Scan on sales_returns sr (cost=0.00..174.43 rows=9,794 width=10) (actual time=0.011..1.960 rows=9,794 loops=1)

  • Filter: (org_key = 1)
24. 0.024 0.059 ↑ 1.0 118 1

Hash (cost=3.48..3.48 rows=118 width=65) (actual time=0.059..0.059 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
25. 0.035 0.035 ↑ 1.0 118 1

Seq Scan on groups (cost=0.00..3.48 rows=118 width=65) (actual time=0.012..0.035 rows=118 loops=1)

  • Filter: (org_key = 1)
26.          

SubPlan (forGroupAggregate)

27. 33.768 33.768 ↑ 1.0 1 16,884

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=16,884)

28. 2.852 1,127.063 ↓ 62.5 250 1

Subquery Scan on f (cost=0.00..21.92 rows=4 width=136) (actual time=1,056.554..1,127.063 rows=250 loops=1)

29. 0.030 1,124.211 ↓ 62.5 250 1

Append (cost=0.00..21.81 rows=4 width=160) (actual time=1,056.521..1,124.211 rows=250 loops=1)

30. 0.058 1,123.172 ↓ 178.0 178 1

Subquery Scan on *SELECT* 1 (cost=0.00..5.39 rows=1 width=160) (actual time=1,056.520..1,123.172 rows=178 loops=1)

31. 1,123.114 1,123.114 ↓ 178.0 178 1

CTE Scan on frame (cost=0.00..5.38 rows=1 width=136) (actual time=1,056.519..1,123.114 rows=178 loops=1)

  • Filter: (store_status = 'open_store'::text)
32. 0.014 0.462 ↓ 68.0 68 1

Subquery Scan on *SELECT* 2 (cost=5.39..5.47 rows=1 width=160) (actual time=0.260..0.462 rows=68 loops=1)

33. 0.192 0.448 ↓ 68.0 68 1

GroupAggregate (cost=5.39..5.46 rows=1 width=160) (actual time=0.259..0.448 rows=68 loops=1)

  • Group Key: frame_1.store, 'All'::text
34. 0.066 0.256 ↓ 178.0 178 1

Sort (cost=5.39..5.39 rows=1 width=96) (actual time=0.244..0.256 rows=178 loops=1)

  • Sort Key: frame_1.store
  • Sort Method: quicksort Memory: 46kB
35. 0.190 0.190 ↓ 178.0 178 1

CTE Scan on frame frame_1 (cost=0.00..5.38 rows=1 width=96) (actual time=0.005..0.190 rows=178 loops=1)

  • Filter: (store_status = 'open_store'::text)
36. 0.002 0.320 ↓ 3.0 3 1

Subquery Scan on *SELECT* 3 (cost=5.39..5.47 rows=1 width=160) (actual time=0.248..0.320 rows=3 loops=1)

37. 0.112 0.318 ↓ 3.0 3 1

GroupAggregate (cost=5.39..5.46 rows=1 width=160) (actual time=0.247..0.318 rows=3 loops=1)

  • Group Key: 'Total'::text, frame_2.category
38. 0.068 0.206 ↓ 178.0 178 1

Sort (cost=5.39..5.39 rows=1 width=96) (actual time=0.197..0.206 rows=178 loops=1)

  • Sort Key: frame_2.category
  • Sort Method: quicksort Memory: 38kB
39. 0.138 0.138 ↓ 178.0 178 1

CTE Scan on frame frame_2 (cost=0.00..5.38 rows=1 width=96) (actual time=0.004..0.138 rows=178 loops=1)

  • Filter: (store_status = 'open_store'::text)
40. 0.002 0.227 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0.00..5.46 rows=1 width=160) (actual time=0.226..0.227 rows=1 loops=1)

41. 0.105 0.225 ↑ 1.0 1 1

GroupAggregate (cost=0.00..5.45 rows=1 width=160) (actual time=0.225..0.225 rows=1 loops=1)

  • Group Key: 'Total'::text, 'All'::text
42. 0.120 0.120 ↓ 178.0 178 1

CTE Scan on frame frame_3 (cost=0.00..5.38 rows=1 width=96) (actual time=0.003..0.120 rows=178 loops=1)

  • Filter: (store_status = 'open_store'::text)
Planning time : 4.227 ms