explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oi7F

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 637.855 ↑ 1.0 77 1

Sort (cost=513,719.96..513,720.15 rows=77 width=94) (actual time=637.851..637.855 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.139 637.814 ↑ 1.0 77 1

GroupAggregate (cost=513,710.62..513,717.55 rows=77 width=94) (actual time=637.677..637.814 rows=77 loops=1)

  • Group Key: st.store_key, st.store_name
3. 0.114 637.675 ↑ 1.7 231 1

Sort (cost=513,710.62..513,711.58 rows=385 width=30) (actual time=637.661..637.675 rows=231 loops=1)

  • Sort Key: st.store_key, st.store_name
  • Sort Method: quicksort Memory: 38kB
4. 0.085 637.561 ↑ 1.7 231 1

Nested Loop Left Join (cost=502,588.93..513,694.08 rows=385 width=30) (actual time=433.961..637.561 rows=231 loops=1)

5. 21.398 636.552 ↑ 1.7 231 1

Merge Left Join (cost=502,588.51..510,441.80 rows=385 width=30) (actual time=433.935..636.552 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.103 0.234 ↑ 1.7 231 1

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

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

Nested Loop (cost=0.42..31.64 rows=385 width=22) (actual time=0.021..0.131 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.010..0.051 rows=77 loops=1)

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

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

10. 0.009 0.009 ↑ 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.008..0.009 rows=3 loops=1)

  • Index Cond: ((org_key = 1) AND (option_key = 29,915,642))
11. 31.033 614.920 ↑ 1.0 176,419 1

Materialize (cost=502,540.34..509,479.85 rows=182,170 width=16) (actual time=377.298..614.920 rows=176,419 loops=1)

12. 125.546 583.887 ↑ 1.0 176,419 1

GroupAggregate (cost=502,540.34..507,202.73 rows=182,170 width=16) (actual time=377.296..583.887 rows=176,419 loops=1)

  • Group Key: stock_sales.org_key, stock_sales.store_key, stock_sales.sku_key
13. 268.063 458.341 ↓ 2.1 394,197 1

Sort (cost=502,540.34..503,013.79 rows=189,379 width=12) (actual time=377.285..458.341 rows=394,197 loops=1)

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

Index Scan using stock_sales_date_idx on stock_sales (cost=0.45..482,702.90 rows=189,379 width=12) (actual time=0.034..190.278 rows=394,658 loops=1)

  • Index Cond: (date_key >= ((now())::date - '28 days'::interval))
  • Filter: (org_key = 1)
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))