explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t66K

Settings
# exclusive inclusive rows x rows loops node
1. 1.117 1,290.771 ↑ 2.6 250 1

Sort (cost=90,497.60..90,499.20 rows=640 width=136) (actual time=1,290.757..1,290.771 rows=250 loops=1)

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

CTE frame

3. 0.566 1,285.376 ↑ 1.3 178 1

WindowAgg (cost=90,094.05..90,382.51 rows=239 width=192) (actual time=1,220.208..1,285.376 rows=178 loops=1)

4. 25.793 1,284.810 ↑ 1.3 178 1

GroupAggregate (cost=90,094.05..90,374.15 rows=239 width=181) (actual time=1,219.320..1,284.810 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. 24.773 1,225.249 ↓ 7.9 16,884 1

Sort (cost=90,094.05..90,099.43 rows=2,150 width=165) (actual time=1,218.555..1,225.249 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.659 1,200.476 ↓ 7.9 16,884 1

Hash Join (cost=76,025.95..89,975.05 rows=2,150 width=165) (actual time=1,038.587..1,200.476 rows=16,884 loops=1)

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

Hash Left Join (cost=76,021.00..89,958.99 rows=2,106 width=78) (actual time=1,038.523..1,192.762 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. 70.529 1,181.290 ↓ 8.0 16,884 1

Hash Right Join (cost=75,675.18..88,927.63 rows=2,106 width=74) (actual time=1,034.110..1,181.290 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.863 85.863 ↑ 1.0 406,239 1

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

  • Filter: (org_key = 1)
10. 6.646 1,024.898 ↓ 8.0 16,884 1

Hash (cost=75,638.33..75,638.33 rows=2,106 width=74) (actual time=1,024.898..1,024.898 rows=16,884 loops=1)

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

Hash Join (cost=74,617.09..75,638.33 rows=2,106 width=74) (actual time=972.512..1,018.252 rows=16,884 loops=1)

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

Hash Join (cost=74,202.05..75,203.32 rows=3,802 width=68) (actual time=968.384..1,007.123 rows=19,179 loops=1)

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

Hash Join (cost=64,583.30..64,964.59 rows=3,802 width=66) (actual time=917.083..939.758 rows=19,179 loops=1)

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

HashAggregate (cost=64,571.30..64,872.24 rows=6,336 width=40) (actual time=917.006..933.544 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. 675.513 675.513 ↓ 1.0 556,191 1

Seq Scan on predictions_skus_daily (cost=0.00..57,785.45 rows=542,868 width=13) (actual time=0.010..675.513 rows=556,191 loops=1)

  • Filter: ((org_key = 1) AND (date_key >= (now())::date) AND (date_key <= ((now())::date + '28 days'::interval)))
  • Rows Removed by Filter: 1074024
16. 0.026 0.064 ↑ 1.0 120 1

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

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

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

  • Filter: (org_key = 1)
18. 22.400 51.126 ↑ 1.0 106,380 1

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

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

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

  • Filter: (org_key = 1)
20. 1.345 4.116 ↑ 1.0 8,009 1

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

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

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

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

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

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

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

  • Filter: (org_key = 1)
24. 0.022 0.055 ↑ 1.0 118 1

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

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

Seq Scan on groups (cost=0.00..3.48 rows=118 width=65) (actual time=0.010..0.033 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.001..0.002 rows=1 loops=16,884)

28. 2.823 1,289.654 ↑ 2.6 250 1

Subquery Scan on f (cost=0.00..85.26 rows=640 width=136) (actual time=1,220.249..1,289.654 rows=250 loops=1)

29. 0.032 1,286.831 ↑ 2.6 250 1

Append (cost=0.00..67.66 rows=640 width=160) (actual time=1,220.216..1,286.831 rows=250 loops=1)

30. 0.055 1,285.867 ↑ 1.3 178 1

Subquery Scan on *SELECT* 1 (cost=0.00..7.77 rows=239 width=160) (actual time=1,220.216..1,285.867 rows=178 loops=1)

31. 1,285.812 1,285.812 ↑ 1.3 178 1

CTE Scan on frame (cost=0.00..4.78 rows=239 width=136) (actual time=1,220.214..1,285.812 rows=178 loops=1)

32. 0.014 0.393 ↑ 2.9 68 1

Subquery Scan on *SELECT* 2 (cost=12.55..22.05 rows=200 width=160) (actual time=0.313..0.393 rows=68 loops=1)

33. 0.199 0.379 ↑ 2.9 68 1

HashAggregate (cost=12.55..20.05 rows=200 width=160) (actual time=0.311..0.379 rows=68 loops=1)

  • Group Key: frame_1.store, 'All'::text
34. 0.180 0.180 ↑ 1.3 178 1

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

35. 0.002 0.275 ↑ 66.7 3 1

Subquery Scan on *SELECT* 3 (cost=12.55..22.05 rows=200 width=160) (actual time=0.271..0.275 rows=3 loops=1)

36. 0.129 0.273 ↑ 66.7 3 1

HashAggregate (cost=12.55..20.05 rows=200 width=160) (actual time=0.270..0.273 rows=3 loops=1)

  • Group Key: 'Total'::text, frame_2.category
37. 0.144 0.144 ↑ 1.3 178 1

CTE Scan on frame frame_2 (cost=0.00..4.78 rows=239 width=96) (actual time=0.002..0.144 rows=178 loops=1)

38. 0.001 0.264 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0.00..12.59 rows=1 width=160) (actual time=0.264..0.264 rows=1 loops=1)

39. 0.112 0.263 ↑ 1.0 1 1

GroupAggregate (cost=0.00..12.58 rows=1 width=160) (actual time=0.263..0.263 rows=1 loops=1)

  • Group Key: 'Total'::text, 'All'::text
40. 0.151 0.151 ↑ 1.3 178 1

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

Planning time : 4.172 ms