explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vbPs

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 2,023.817 ↓ 21.0 21 1
→

Sort (cost=55,942.09..55,942.10 rows=1 width=44) (actual time=2,023.816..2,023.817 rows=21 loops=1)

  • Sort Key: (concat(p.year, CASE WHEN (p.week < 10) THEN '-W0'::text ELSE '-W'::text END, p.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.028 2,023.780 ↓ 21.0 21 1
→

Hash Join (cost=55,938.68..55,942.07 rows=1 width=44) (actual time=2,023.759..2,023.780 rows=21 loops=1)

  • Hash Cond: ((calendar.year = p.year) AND (calendar.week = p.week))
5. 0.040 0.086 ↑ 6.1 22 1
→

HashAggregate (cost=346.81..348.15 rows=134 width=4) (actual time=0.080..0.086 rows=22 loops=1)

  • Group Key: calendar.year, calendar.week
6. 0.016 0.046 ↓ 1.0 140 1
→

Bitmap Heap Scan on calendar (cost=6.05..346.12 rows=137 width=4) (actual time=0.034..0.046 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
7. 0.030 0.030 ↓ 1.0 140 1
→

Bitmap Index Scan on calendar_pkey (cost=0.00..6.02 rows=137 width=0) (actual time=0.030..0.030 rows=140 loops=1)

  • Index Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
8. 0.016 2,023.666 ↑ 2.0 52 1
→

Hash (cost=55,590.31..55,590.31 rows=104 width=16) (actual time=2,023.666..2,023.666 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
9. 220.116 2,023.650 ↑ 2.0 52 1
→

HashAggregate (cost=55,587.97..55,589.27 rows=104 width=16) (actual time=2,023.636..2,023.650 rows=52 loops=1)

  • Group Key: p.year, p.week
10. 362.140 1,803.534 ↓ 7.0 447,772 1
→

Nested Loop Left Join (cost=396.69..54,312.39 rows=63,779 width=24) (actual time=6.944..1,803.534 rows=447,772 loops=1)

11. 134.433 545.850 ↓ 7.0 447,772 1
→

Nested Loop (cost=396.26..16,919.17 rows=63,779 width=22) (actual time=6.918..545.850 rows=447,772 loops=1)

  • Join Filter: CASE WHEN (array_length(f.groups, 1) IS NULL) THEN true ELSE (p.group_key = ANY (f.groups)) END
12. 0.003 0.003 ↑ 1.0 1 1
→

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

13. 159.825 411.414 ↓ 3.5 447,772 1
→

Hash Join (cost=396.26..13,730.17 rows=127,559 width=22) (actual time=6.912..411.414 rows=447,772 loops=1)

  • Hash Cond: ((st.store_key = sc.store_key) AND (p.group_key = sc.group_key))
14. 158.536 244.761 ↓ 2.0 484,744 1
→

Hash Join (cost=12.26..12,065.25 rows=243,956 width=20) (actual time=0.063..244.761 rows=484,744 loops=1)

  • Hash Cond: (p.store_key = st.store_key)
15. 86.170 86.170 ↑ 1.0 484,744 1
→

Seq Scan on predictions_stores_groups_weekly p (cost=0.00..10,745.30 rows=484,744 width=18) (actual time=0.004..86.170 rows=484,744 loops=1)

  • Filter: (org_key = 1)
16. 0.013 0.055 ↓ 1.0 79 1
→

Hash (cost=11.29..11.29 rows=77 width=4) (actual time=0.055..0.055 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.042 0.042 ↓ 1.0 79 1
→

Seq Scan on stores st (cost=0.00..11.29 rows=77 width=4) (actual time=0.005..0.042 rows=79 loops=1)

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 74
18. 2.940 6.828 ↑ 1.0 10,400 1
→

Hash (cost=228.00..228.00 rows=10,400 width=12) (actual time=6.828..6.828 rows=10,400 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 575kB
19. 3.888 3.888 ↑ 1.0 10,400 1
→

Seq Scan on stock_current_groups sc (cost=0.00..228.00 rows=10,400 width=12) (actual time=0.005..3.888 rows=10,400 loops=1)

  • Filter: (org_key = 1)
20. 895.544 895.544 ↓ 0.0 0 447,772
→

Index Scan using stores_groups_weekly_org_key_store_key_group_key_year_week_idx on stores_groups_weekly sg (cost=0.43..0.59 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=447,772)

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