explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IpuN

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 1,669.513 ↑ 5.0 21 1

Subquery Scan on f (cost=14,379.31..14,392.88 rows=104 width=44) (actual time=1,571.795..1,669.513 rows=21 loops=1)

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. 69.920 1,669.480 ↑ 5.0 21 1

GroupAggregate (cost=14,379.30..14,391.31 rows=104 width=16) (actual time=1,571.786..1,669.480 rows=21 loops=1)

  • Group Key: p.year, p.week
5. 100.485 1,599.560 ↓ 379.9 180,831 1

Sort (cost=14,379.30..14,380.49 rows=476 width=24) (actual time=1,566.943..1,599.560 rows=180,831 loops=1)

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 5128kB
6. 177.667 1,499.075 ↓ 379.9 180,831 1

Nested Loop Left Join (cost=364.61..14,358.13 rows=476 width=24) (actual time=0.362..1,499.075 rows=180,831 loops=1)

7. 81.375 959.746 ↓ 379.9 180,831 1

Nested Loop (cost=364.18..14,079.05 rows=476 width=22) (actual time=0.353..959.746 rows=180,831 loops=1)

8. 203.862 697.540 ↓ 379.9 180,831 1

Nested Loop (cost=364.04..14,001.77 rows=476 width=24) (actual time=0.348..697.540 rows=180,831 loops=1)

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

Nested Loop (cost=363.75..13,709.60 rows=910 width=20) (actual time=0.339..297.916 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.581 236.412 ↓ 107.5 195,762 1

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

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

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

  • Hash Cond: ((p.year = calendar.year) AND (p.week = calendar.week))
13. 69.848 69.848 ↑ 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..69.848 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.037..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.031..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.013 0.062 ↓ 1.0 79 1

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

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

Seq Scan on stores st (cost=0.00..11.29 rows=77 width=4) (actual time=0.009..0.049 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. 180.831 180.831 ↑ 1.0 1 180,831

Index Only Scan using groups_pkey on groups g (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=180,831)

  • Index Cond: ((org_key = 1) AND (group_key = p.group_key))
  • Heap Fetches: 180831
22. 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 : 4.501 ms