explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LhdG

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 5,234.984 ↑ 5.0 21 1

Sort (cost=75,274.86..75,275.12 rows=104 width=44) (actual time=5,234.983..5,234.984 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.027 5,234.962 ↑ 5.0 21 1

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

5. 61.074 5,234.935 ↑ 5.0 21 1

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

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

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

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

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

8. 553.990 2,767.674 ↓ 141.8 579,852 1

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

9. 181.334 1,053.980 ↓ 141.8 579,852 1

Nested Loop (cost=365.97..57,134.38 rows=4,088 width=16) (actual time=0.173..1,053.980 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. 163.747 872.642 ↓ 70.9 579,852 1

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

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

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

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

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

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

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

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

  • Group Key: calendar.year, calendar.week
16. 0.017 0.051 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.07..348.23 rows=139 width=4) (actual time=0.037..0.051 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.061 ↑ 1.0 78 1

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

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

Seq Scan on stores st (cost=0.00..11.29 rows=79 width=4) (actual time=0.007..0.049 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.003..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))