explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kBr

Settings
# exclusive inclusive rows x rows loops node
1. 1.183 1,348.483 ↓ 62.5 250 1

Sort (cost=90,404.48..90,404.49 rows=4 width=136) (actual time=1,348.471..1,348.483 rows=250 loops=1)

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

CTE frame

3. 0.951 1,342.316 ↑ 1.3 178 1

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

4. 37.791 1,341.365 ↑ 1.3 178 1

GroupAggregate (cost=90,094.05..90,374.15 rows=239 width=181) (actual time=1,262.294..1,341.365 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. 26.186 1,269.806 ↓ 7.9 16,884 1

Sort (cost=90,094.05..90,099.43 rows=2,150 width=165) (actual time=1,261.537..1,269.806 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.655 1,243.620 ↓ 7.9 16,884 1

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

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

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

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

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

  • Filter: (org_key = 1)
10. 6.456 1,071.679 ↓ 8.0 16,884 1

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

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

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

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

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

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

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

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

HashAggregate (cost=64,571.30..64,872.24 rows=6,336 width=40) (actual time=963.644..979.690 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. 711.469 711.469 ↓ 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..711.469 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.027 0.066 ↑ 1.0 120 1

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

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

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

  • Filter: (org_key = 1)
18. 24.856 53.539 ↑ 1.0 106,380 1

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

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

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

  • Filter: (org_key = 1)
20. 1.295 3.731 ↑ 1.0 8,009 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 409kB
21. 2.436 2.436 ↑ 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..2.436 rows=8,009 loops=1)

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

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

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

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

  • Filter: (org_key = 1)
24. 0.023 0.057 ↑ 1.0 118 1

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

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

Seq Scan on groups (cost=0.00..3.48 rows=118 width=65) (actual time=0.011..0.034 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. 3.183 1,347.300 ↓ 62.5 250 1

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

29. 0.038 1,344.117 ↓ 62.5 250 1

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

30. 0.074 1,343.074 ↓ 178.0 178 1

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

31. 1,343.000 1,343.000 ↓ 178.0 178 1

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

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

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

33. 0.189 0.447 ↓ 68.0 68 1

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

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

Sort (cost=5.39..5.39 rows=1 width=96) (actual time=0.247..0.258 rows=178 loops=1)

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

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

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

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

37. 0.112 0.315 ↓ 3.0 3 1

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

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

Sort (cost=5.39..5.39 rows=1 width=96) (actual time=0.194..0.203 rows=178 loops=1)

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

CTE Scan on frame frame_2 (cost=0.00..5.38 rows=1 width=96) (actual time=0.003..0.134 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.106 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.119 0.119 ↓ 178.0 178 1

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

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