explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AdO7

Settings
# exclusive inclusive rows x rows loops node
1. 1.049 1,179.110 ↓ 62.5 250 1

Sort (cost=87,145.82..87,145.83 rows=4 width=136) (actual time=1,179.097..1,179.110 rows=250 loops=1)

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

CTE frame

3. 0.556 1,173.662 ↓ 1.1 178 1

WindowAgg (cost=86,931.02..87,130.52 rows=165 width=192) (actual time=1,108.719..1,173.662 rows=178 loops=1)

4. 26.088 1,173.106 ↓ 1.1 178 1

GroupAggregate (cost=86,931.02..87,124.74 rows=165 width=181) (actual time=1,107.790..1,173.106 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.814 1,113.250 ↓ 11.4 16,884 1

Sort (cost=86,931.02..86,934.74 rows=1,487 width=165) (actual time=1,107.050..1,113.250 rows=16,884 loops=1)

  • Sort Key: st.store_key, st.store_name, ((groups.properties ->> '1'::text))
  • Sort Method: external merge Disk: 2144kB
6. 8.936 1,087.436 ↓ 11.4 16,884 1

Hash Join (cost=65,349.67..86,852.67 rows=1,487 width=165) (actual time=951.493..1,087.436 rows=16,884 loops=1)

  • Hash Cond: (sk.group_key = groups.group_key)
7. 9.143 1,078.440 ↓ 11.6 16,884 1

Hash Left Join (cost=65,344.72..86,840.04 rows=1,456 width=78) (actual time=951.426..1,078.440 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. 3.529 1,065.620 ↓ 11.6 16,884 1

Nested Loop Left Join (cost=64,998.90..86,020.26 rows=1,456 width=74) (actual time=947.722..1,065.620 rows=16,884 loops=1)

9. 7.786 1,011.439 ↓ 11.6 16,884 1

Hash Join (cost=64,998.48..73,927.57 rows=1,456 width=74) (actual time=947.707..1,011.439 rows=16,884 loops=1)

  • Hash Cond: ((sk.group_key = ss.group_key) AND (st.store_key = ss.store_key))
10. 12.589 999.739 ↓ 7.2 18,960 1

Nested Loop (cost=64,583.43..73,498.72 rows=2,629 width=68) (actual time=943.781..999.739 rows=18,960 loops=1)

11. 6.139 968.190 ↓ 7.2 18,960 1

Hash Join (cost=64,583.14..64,964.43 rows=2,629 width=66) (actual time=943.759..968.190 rows=18,960 loops=1)

  • Hash Cond: (predictions_skus_daily.store_key = st.store_key)
12. 263.567 961.992 ↓ 3.0 19,179 1

HashAggregate (cost=64,571.30..64,872.24 rows=6,336 width=40) (actual time=943.691..961.992 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)
13. 698.425 698.425 ↓ 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..698.425 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
14. 0.017 0.059 ↑ 1.0 80 1

Hash (cost=10.80..10.80 rows=83 width=26) (actual time=0.059..0.059 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
15. 0.042 0.042 ↑ 1.0 80 1

Seq Scan on stores st (cost=0.00..10.80 rows=83 width=26) (actual time=0.007..0.042 rows=80 loops=1)

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 40
16. 18.960 18.960 ↑ 1.0 1 18,960

Index Scan using skus_pkey on skus sk (cost=0.29..3.25 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=18,960)

  • Index Cond: ((org_key = 1) AND (sku_key = predictions_skus_daily.sku_key))
17. 1.370 3.914 ↑ 1.0 8,009 1

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

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

  • Filter: ((stock_factor > '0'::double precision) AND (org_key = 1))
  • Rows Removed by Filter: 1785
19. 50.652 50.652 ↑ 1.0 1 16,884

Index Scan using stock_current_pkey on stock_current (cost=0.42..8.31 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=16,884)

  • Index Cond: ((org_key = sk.org_key) AND (org_key = 1) AND (store_key = st.store_key) AND (sku_key = sk.sku_key))
20. 1.747 3.677 ↑ 1.0 9,794 1

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

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

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

  • Filter: (org_key = 1)
22. 0.025 0.060 ↑ 1.0 118 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
23. 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)
24.          

SubPlan (forGroupAggregate)

25. 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)

26. 2.838 1,178.061 ↓ 62.5 250 1

Subquery Scan on f (cost=0.00..15.26 rows=4 width=136) (actual time=1,108.759..1,178.061 rows=250 loops=1)

27. 0.031 1,175.223 ↓ 62.5 250 1

Append (cost=0.00..15.15 rows=4 width=160) (actual time=1,108.727..1,175.223 rows=250 loops=1)

28. 0.056 1,174.200 ↓ 178.0 178 1

Subquery Scan on *SELECT* 1 (cost=0.00..3.73 rows=1 width=160) (actual time=1,108.727..1,174.200 rows=178 loops=1)

29. 1,174.144 1,174.144 ↓ 178.0 178 1

CTE Scan on frame (cost=0.00..3.71 rows=1 width=136) (actual time=1,108.725..1,174.144 rows=178 loops=1)

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

Subquery Scan on *SELECT* 2 (cost=3.72..3.81 rows=1 width=160) (actual time=0.247..0.447 rows=68 loops=1)

31. 0.191 0.433 ↓ 68.0 68 1

GroupAggregate (cost=3.72..3.80 rows=1 width=160) (actual time=0.245..0.433 rows=68 loops=1)

  • Group Key: frame_1.store, 'All'::text
32. 0.067 0.242 ↓ 178.0 178 1

Sort (cost=3.72..3.73 rows=1 width=96) (actual time=0.231..0.242 rows=178 loops=1)

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

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

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

Subquery Scan on *SELECT* 3 (cost=3.72..3.81 rows=1 width=160) (actual time=0.241..0.313 rows=3 loops=1)

35. 0.112 0.311 ↓ 3.0 3 1

GroupAggregate (cost=3.72..3.80 rows=1 width=160) (actual time=0.240..0.311 rows=3 loops=1)

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

Sort (cost=3.72..3.73 rows=1 width=96) (actual time=0.189..0.199 rows=178 loops=1)

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

CTE Scan on frame frame_2 (cost=0.00..3.71 rows=1 width=96) (actual time=0.003..0.131 rows=178 loops=1)

  • Filter: (store_status = 'open_store'::text)
38. 0.001 0.232 ↑ 1.0 1 1

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

39. 0.108 0.231 ↑ 1.0 1 1

GroupAggregate (cost=0.00..3.78 rows=1 width=160) (actual time=0.231..0.231 rows=1 loops=1)

  • Group Key: 'Total'::text, 'All'::text
40. 0.123 0.123 ↓ 178.0 178 1

CTE Scan on frame frame_3 (cost=0.00..3.71 rows=1 width=96) (actual time=0.002..0.123 rows=178 loops=1)

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