explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xp8S

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 1,388.447 ↑ 5.0 21 1

Sort (cost=14,319.08..14,319.34 rows=104 width=44) (actual time=1,388.446..1,388.447 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.032 1,388.419 ↑ 5.0 21 1

Subquery Scan on f (cost=14,302.01..14,315.58 rows=104 width=44) (actual time=1,289.268..1,388.419 rows=21 loops=1)

5. 71.334 1,388.387 ↑ 5.0 21 1

GroupAggregate (cost=14,302.01..14,314.02 rows=104 width=16) (actual time=1,289.259..1,388.387 rows=21 loops=1)

  • Group Key: p.year, p.week
6. 100.590 1,317.053 ↓ 379.9 180,831 1

Sort (cost=14,302.01..14,303.20 rows=476 width=24) (actual time=1,284.491..1,317.053 rows=180,831 loops=1)

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 5128kB
7. 169.010 1,216.463 ↓ 379.9 180,831 1

Nested Loop Left Join (cost=364.47..14,280.84 rows=476 width=24) (actual time=0.340..1,216.463 rows=180,831 loops=1)

8. 195.160 685.791 ↓ 379.9 180,831 1

Nested Loop (cost=364.04..14,001.77 rows=476 width=22) (actual time=0.332..685.791 rows=180,831 loops=1)

  • Join Filter: (st.store_key = sc.store_key)
9. 61.029 294.869 ↓ 215.1 195,762 1

Nested Loop (cost=363.75..13,709.60 rows=910 width=20) (actual time=0.323..294.869 rows=195,762 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. 57.905 233.836 ↓ 107.5 195,762 1

Hash Join (cost=363.75..13,664.05 rows=1,821 width=20) (actual time=0.318..233.836 rows=195,762 loops=1)

  • Hash Cond: (p.store_key = st.store_key)
12. 108.516 175.872 ↓ 54.1 195,762 1

Hash Join (cost=351.50..13,642.03 rows=3,617 width=18) (actual time=0.254..175.872 rows=195,762 loops=1)

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

  • Filter: (org_key = 1)
14. 0.004 0.091 ↑ 6.1 22 1

Hash (cost=349.49..349.49 rows=134 width=4) (actual time=0.091..0.091 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.038 0.087 ↑ 6.1 22 1

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

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

Bitmap Heap Scan on calendar (cost=6.05..346.12 rows=137 width=4) (actual time=0.036..0.049 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.032 0.032 ↓ 1.0 140 1

Bitmap Index Scan on calendar_pkey (cost=0.00..6.02 rows=137 width=0) (actual time=0.032..0.032 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.059 ↓ 1.0 79 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.047 0.047 ↓ 1.0 79 1

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

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 74
20. 195.762 195.762 ↑ 1.0 1 195,762

Index Scan using stock_current_groups_idx on stock_current_groups sc (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=195,762)

  • Index Cond: ((org_key = 1) AND (store_key = p.store_key) AND (group_key = p.group_key))
21. 361.662 361.662 ↓ 0.0 0 180,831

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=180,831)

  • 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.726 ms