explain.depesz.com

PostgreSQL's explain analyze made readable

Result: klTo

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 5,244.819 ↑ 5.0 21 1

Sort (cost=75,274.86..75,275.12 rows=104 width=44) (actual time=5,244.817..5,244.819 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.001 0.001 ↑ 1.0 1 1

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

4. 0.036 5,244.792 ↑ 5.0 21 1

Subquery Scan on f (cost=75,217.41..75,271.37 rows=104 width=44) (actual time=5,150.262..5,244.792 rows=21 loops=1)

5. 60.703 5,244.756 ↑ 5.0 21 1

GroupAggregate (cost=75,217.41..75,269.81 rows=104 width=16) (actual time=5,150.253..5,244.756 rows=21 loops=1)

  • Group Key: p.year, p.week
6. 115.855 5,184.053 ↓ 53.7 219,387 1

Sort (cost=75,217.41..75,227.63 rows=4,088 width=12) (actual time=5,146.082..5,184.053 rows=219,387 loops=1)

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 4,248kB
7. 0.000 5,068.198 ↓ 53.7 219,387 1

Nested Loop (cost=366.83..74,972.19 rows=4,088 width=12) (actual time=0.259..5,068.198 rows=219,387 loops=1)

8. 575.491 2,776.366 ↓ 141.8 579,852 1

Nested Loop Left Join (cost=366.40..59,304.93 rows=4,088 width=20) (actual time=0.191..2,776.366 rows=579,852 loops=1)

9. 176.822 1,041.171 ↓ 141.8 579,852 1

Nested Loop (cost=365.97..57,134.38 rows=4,088 width=16) (actual time=0.181..1,041.171 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.424 864.345 ↓ 70.9 579,852 1

Hash Join (cost=365.97..56,929.93 rows=8,177 width=16) (actual time=0.175..864.345 rows=579,852 loops=1)

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

Hash Join (cost=353.69..56,874.92 rows=15,836 width=16) (actual time=0.102..703.857 rows=579,852 loops=1)

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

Seq Scan on predictions_stores_dims_weekly p (cost=0.00..45,212.70 rows=2,153,736 width=16) (actual time=0.004..288.548 rows=2,153,736 loops=1)

  • Filter: (org_key = 1)
14. 0.004 0.093 ↑ 6.2 22 1

Hash (cost=351.65..351.65 rows=136 width=4) (actual time=0.092..0.093 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.039 0.089 ↑ 6.2 22 1

HashAggregate (cost=348.93..350.29 rows=136 width=4) (actual time=0.084..0.089 rows=22 loops=1)

  • Group Key: calendar.year, calendar.week
16. 0.016 0.050 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.07..348.23 rows=139 width=4) (actual time=0.037..0.050 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.034 0.034 ↓ 1.0 140 1

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

Hash (cost=11.29..11.29 rows=79 width=4) (actual time=0.064..0.064 rows=78 loops=1)

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

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

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 75
20. 1,159.704 1,159.704 ↓ 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.002..0.002 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. 579.852 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. 0.000 1,739.556 ↓ 0.0 0 579,852

Limit (cost=0.43..3.81 rows=1 width=8) (actual time=0.003..0.003 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))