explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgHl

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 5,708.790 ↑ 5.0 21 1

Sort (cost=84,202.47..84,202.73 rows=104 width=44) (actual time=5,708.789..5,708.790 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.029 5,708.766 ↑ 5.0 21 1

Subquery Scan on f (cost=84,145.66..84,198.98 rows=104 width=44) (actual time=5,618.313..5,708.766 rows=21 loops=1)

5. 60.667 5,708.737 ↑ 5.0 21 1

GroupAggregate (cost=84,145.66..84,197.42 rows=104 width=16) (actual time=5,618.303..5,708.737 rows=21 loops=1)

  • Group Key: p.year, p.week
6. 96.463 5,648.070 ↓ 53.7 216,809 1

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

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 4176kB
7. 119.939 5,551.607 ↓ 53.7 216,809 1

Nested Loop (cost=367.81..83,903.86 rows=4,037 width=12) (actual time=380.102..5,551.607 rows=216,809 loops=1)

8. 230.290 3,112.260 ↓ 143.6 579,852 1

Nested Loop Left Join (cost=367.39..68,432.06 rows=4,037 width=20) (actual time=380.030..3,112.260 rows=579,852 loops=1)

9. 186.133 1,142.414 ↓ 143.6 579,852 1

Nested Loop (cost=366.96..66,278.77 rows=4,037 width=16) (actual time=379.993..1,142.414 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.002..0.004 rows=1 loops=1)

11. 162.898 956.277 ↓ 71.8 579,852 1

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

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

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

  • Hash Cond: ((p.year = calendar.week_year) AND (p.week = calendar.week_week))
13. 355.648 355.648 ↑ 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=62.394..355.648 rows=2,153,736 loops=1)

  • Filter: (org_key = 1)
14. 0.007 0.124 ↑ 6.5 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.047 0.117 ↑ 6.5 21 1

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

  • Group Key: calendar.week_year, calendar.week_week
16. 0.027 0.070 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.07..348.23 rows=139 width=4) (actual time=0.056..0.070 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.043 0.043 ↓ 1.0 140 1

Bitmap Index Scan on calendar_pkey (cost=0.00..6.04 rows=139 width=0) (actual time=0.043..0.043 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.018 0.066 ↑ 1.0 78 1

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

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

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

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

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=579,852)

  • 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))
21. 0.000 2,319.408 ↓ 0.0 0 579,852

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

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

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

23. 1,739.556 1,739.556 ↓ 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.003..0.003 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))
Planning time : 1.268 ms