explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yfJw

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 32,666.458 ↑ 1.0 77 1

Sort (cost=229,355.39..229,355.58 rows=77 width=94) (actual time=32,666.454..32,666.458 rows=77 loops=1)

  • Sort Key: (COALESCE(sum((COALESCE(sum((stock_sales.sales - stock_sales.returns)), '0'::bigint))), '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 34kB
2. 0.158 32,666.409 ↑ 1.0 77 1

GroupAggregate (cost=229,346.05..229,352.98 rows=77 width=94) (actual time=32,666.271..32,666.409 rows=77 loops=1)

  • Group Key: st.store_key, st.store_name
3. 0.152 32,666.251 ↑ 1.7 231 1

Sort (cost=229,346.05..229,347.01 rows=385 width=30) (actual time=32,666.237..32,666.251 rows=231 loops=1)

  • Sort Key: st.store_key, st.store_name
  • Sort Method: quicksort Memory: 38kB
4. 1.972 32,666.099 ↑ 1.7 231 1

Nested Loop Left Join (cost=218,981.70..229,329.52 rows=385 width=30) (actual time=32,406.973..32,666.099 rows=231 loops=1)

5. 27.695 32,663.203 ↑ 1.7 231 1

Merge Left Join (cost=218,981.28..226,077.23 rows=385 width=30) (actual time=32,406.946..32,663.203 rows=231 loops=1)

  • Merge Cond: ((st.store_key = stock_sales.store_key) AND (sk.sku_key = stock_sales.sku_key))
  • Join Filter: (sk.org_key = stock_sales.org_key)
6. 0.148 0.284 ↑ 1.7 231 1

Sort (cost=48.17..49.13 rows=385 width=22) (actual time=0.233..0.284 rows=231 loops=1)

  • Sort Key: st.store_key, sk.sku_key
  • Sort Method: quicksort Memory: 42kB
7. 0.008 0.136 ↑ 1.7 231 1

Nested Loop (cost=0.42..31.64 rows=385 width=22) (actual time=0.026..0.136 rows=231 loops=1)

8. 0.051 0.051 ↑ 1.0 77 1

Seq Scan on stores st (cost=0.00..11.29 rows=77 width=16) (actual time=0.008..0.051 rows=77 loops=1)

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 75
9. 0.063 0.077 ↑ 1.7 3 77

Materialize (cost=0.42..15.54 rows=5 width=8) (actual time=0.000..0.001 rows=3 loops=77)

10. 0.014 0.014 ↑ 1.7 3 1

Index Scan using sku_option_idx on skus sk (cost=0.42..15.52 rows=5 width=8) (actual time=0.013..0.014 rows=3 loops=1)

  • Index Cond: ((org_key = 1) AND (option_key = 29,915,642))
11. 35.093 32,635.224 ↓ 1.1 176,419 1

Materialize (cost=218,933.11..225,202.02 rows=164,823 width=16) (actual time=32,350.020..32,635.224 rows=176,419 loops=1)

12. 150.665 32,600.131 ↓ 1.1 176,419 1

GroupAggregate (cost=218,933.11..223,141.73 rows=164,823 width=16) (actual time=32,350.016..32,600.131 rows=176,419 loops=1)

  • Group Key: stock_sales.org_key, stock_sales.store_key, stock_sales.sku_key
13. 309.061 32,449.466 ↓ 2.3 394,197 1

Sort (cost=218,933.11..219,359.84 rows=170,693 width=12) (actual time=32,350.008..32,449.466 rows=394,197 loops=1)

  • Sort Key: stock_sales.store_key, stock_sales.sku_key
  • Sort Method: external merge Disk: 8,528kB
14. 32,140.405 32,140.405 ↓ 2.3 394,658 1

Index Scan using stock_sales_org_date_desc_idx on stock_sales (cost=0.45..201,179.99 rows=170,693 width=12) (actual time=0.019..32,140.405 rows=394,658 loops=1)

  • Index Cond: ((org_key = 1) AND (date_key >= ((now())::date - '28 days'::interval)))
15. 0.924 0.924 ↓ 0.0 0 231

Index Scan using stock_current_pkey on stock_current sc (cost=0.42..8.45 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=231)

  • Index Cond: ((sk.org_key = org_key) AND (org_key = 1) AND (st.store_key = store_key) AND (sk.sku_key = sku_key))