explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wgPk

Settings
# exclusive inclusive rows x rows loops node
1. 71.287 1,832.854 ↑ 5.0 21 1

GroupAggregate (cost=14,381.69..14,393.03 rows=104 width=44) (actual time=1,730.664..1,832.854 rows=21 loops=1)

  • Group Key: (concat(p.year, CASE WHEN (p.week < 10) THEN '-W0'::text ELSE '-W'::text END, p.week))
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. 169.005 1,761.567 ↓ 379.9 180,831 1

Sort (cost=14,381.68..14,382.87 rows=476 width=52) (actual time=1,725.882..1,761.567 rows=180,831 loops=1)

  • Sort Key: (concat(p.year, CASE WHEN (p.week < 10) THEN '-W0'::text ELSE '-W'::text END, p.week))
  • Sort Method: external merge Disk: 6544kB
5. 262.165 1,592.562 ↓ 379.9 180,831 1

Nested Loop Left Join (cost=364.61..14,360.51 rows=476 width=52) (actual time=0.411..1,592.562 rows=180,831 loops=1)

6. 78.399 968.735 ↓ 379.9 180,831 1

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

7. 206.965 709.505 ↓ 379.9 180,831 1

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

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

Nested Loop (cost=363.75..13,709.60 rows=910 width=20) (actual time=0.367..306.778 rows=195,762 loops=1)

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

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

10. 58.971 240.615 ↓ 107.5 195,762 1

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

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

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

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

  • Filter: (org_key = 1)
13. 0.006 0.100 ↑ 6.1 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.038 0.094 ↑ 6.1 22 1

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

  • Group Key: calendar.year, calendar.week
15. 0.016 0.056 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.05..346.12 rows=137 width=4) (actual time=0.045..0.056 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.040 0.040 ↓ 1.0 140 1

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

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

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

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

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 74
19. 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))
20. 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
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 : 5.477 ms