explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KrpF

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 1,868.643 ↑ 5.0 21 1

Subquery Scan on f (cost=14,302.02..14,315.59 rows=104 width=44) (actual time=1,773.190..1,868.643 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. 54.070 1,868.602 ↑ 5.0 21 1

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

  • Group Key: p.year, p.week
5. 139.536 1,814.532 ↓ 379.9 180,831 1

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

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

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

7. 167.645 924.440 ↓ 379.9 180,831 1

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

  • Join Filter: (st.store_key = sc.store_key)
8. 60.072 365.271 ↓ 215.1 195,762 1

Nested Loop (cost=363.75..13,709.60 rows=910 width=20) (actual time=0.353..365.271 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
9. 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)

10. 64.395 305.195 ↓ 107.5 195,762 1

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

  • Hash Cond: (p.store_key = st.store_key)
11. 149.767 240.739 ↓ 54.1 195,762 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.039 0.087 ↑ 6.1 22 1

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

  • Group Key: calendar.year, calendar.week
15. 0.017 0.048 ↓ 1.0 140 1

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

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

  • Index Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
17. 0.013 0.061 ↓ 1.0 79 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
18. 0.048 0.048 ↓ 1.0 79 1

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

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 74
19. 391.524 391.524 ↑ 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.002..0.002 rows=1 loops=195,762)

  • Index Cond: ((org_key = 1) AND (store_key = p.store_key) AND (group_key = p.group_key))
20. 542.493 542.493 ↓ 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.003..0.003 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.957 ms