explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bWeW

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 4,850.871 ↑ 5.0 21 1

Sort (cost=84,202.47..84,202.73 rows=104 width=44) (actual time=4,850.870..4,850.871 rows=21 loops=1)

  • Sort Key: (concat(f.year, CASE WHEN (f.week < 10) THEN '-W0'::text ELSE '-W'::text END, f.week))
  • Sort Method: quicksort Memory: 26kB
2.          

CTE f

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

4. 0.027 4,850.847 ↑ 5.0 21 1

Subquery Scan on f (cost=84,145.66..84,198.98 rows=104 width=44) (actual time=4,762.354..4,850.847 rows=21 loops=1)

5. 60.347 4,850.820 ↑ 5.0 21 1

GroupAggregate (cost=84,145.66..84,197.42 rows=104 width=16) (actual time=4,762.345..4,850.820 rows=21 loops=1)

  • Group Key: p.year, p.week
6. 109.740 4,790.473 ↓ 53.7 216,809 1

Sort (cost=84,145.66..84,155.75 rows=4,037 width=12) (actual time=4,758.542..4,790.473 rows=216,809 loops=1)

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 4176kB
7. 60.579 4,680.733 ↓ 53.7 216,809 1

Nested Loop Left Join (cost=367.81..83,903.86 rows=4,037 width=12) (actual time=295.374..4,680.733 rows=216,809 loops=1)

8. 10.153 3,969.727 ↓ 53.7 216,809 1

Nested Loop (cost=367.39..81,750.57 rows=4,037 width=16) (actual time=295.362..3,969.727 rows=216,809 loops=1)

9. 185.130 1,060.314 ↓ 143.6 579,852 1

Nested Loop (cost=366.96..66,278.77 rows=4,037 width=16) (actual time=295.303..1,060.314 rows=579,852 loops=1)

  • Join Filter: CASE WHEN (array_length(f_1.groups, 1) IS NULL) THEN true ELSE (p.group_key = ANY (f_1.groups)) END
10. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on f f_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)

11. 160.189 875.180 ↓ 71.8 579,852 1

Hash Join (cost=366.96..66,076.92 rows=8,073 width=16) (actual time=295.294..875.180 rows=579,852 loops=1)

  • Hash Cond: (p.store_key = st.store_key)
12. 425.935 714.926 ↓ 36.6 579,852 1

Hash Join (cost=353.69..66,020.92 rows=15,836 width=16) (actual time=295.214..714.926 rows=579,852 loops=1)

  • Hash Cond: ((p.year = calendar.week_year) AND (p.week = calendar.week_week))
13. 288.881 288.881 ↑ 1.0 2,153,736 1

Seq Scan on predictions_stores_dims_weekly p (cost=0.00..54,358.70 rows=2,153,736 width=16) (actual time=10.614..288.881 rows=2,153,736 loops=1)

  • Filter: (org_key = 1)
14. 0.006 0.110 ↑ 6.5 21 1

Hash (cost=351.65..351.65 rows=136 width=4) (actual time=0.110..0.110 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.046 0.104 ↑ 6.5 21 1

HashAggregate (cost=348.93..350.29 rows=136 width=4) (actual time=0.100..0.104 rows=21 loops=1)

  • Group Key: calendar.week_year, calendar.week_week
16. 0.019 0.058 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.07..348.23 rows=139 width=4) (actual time=0.044..0.058 rows=140 loops=1)

  • Recheck Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
  • Heap Blocks: exact=2
17. 0.039 0.039 ↓ 1.0 140 1

Bitmap Index Scan on calendar_pkey (cost=0.00..6.04 rows=139 width=0) (actual time=0.039..0.039 rows=140 loops=1)

  • Index Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
18. 0.013 0.065 ↑ 1.0 78 1

Hash (cost=12.29..12.29 rows=78 width=4) (actual time=0.064..0.065 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.052 0.052 ↑ 1.0 78 1

Seq Scan on stores st (cost=0.00..12.29 rows=78 width=4) (actual time=0.009..0.052 rows=78 loops=1)

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 75
20. 579.852 2,899.260 ↓ 0.0 0 579,852

Subquery Scan on ls (cost=0.43..3.82 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=579,852)

  • Filter: (ls.stock > 0)
  • Rows Removed by Filter: 0
21. 0.000 2,319.408 ↓ 0.0 0 579,852

Limit (cost=0.43..3.81 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=579,852)

22. 2,319.408 2,319.408 ↓ 0.0 0 579,852

Index Scan Backward using stores_dims_weekly_pkey on stores_dims_weekly (cost=0.43..13.96 rows=4 width=8) (actual time=0.004..0.004 rows=0 loops=579,852)

  • Index Cond: ((org_key = p.org_key) AND (store_key = p.store_key) AND (group_key = p.group_key) AND (collection_key = p.collection_key) AND (year <= p.year) AND (week <= p.week))
23. 650.427 650.427 ↓ 0.0 0 216,809

Index Scan using stores_dims_weekly_pkey on stores_dims_weekly sg (cost=0.43..0.53 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=216,809)

  • Index Cond: ((org_key = p.org_key) AND (org_key = 1) AND (store_key = p.store_key) AND (group_key = p.group_key) AND (collection_key = p.collection_key) AND (year = p.year) AND (week = p.week))
Planning time : 1.852 ms