#	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: 1,024 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: 1,024 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: 16,384 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))

"Sort  (cost=55942.09..55942.10 rows=1 width=44) (actual time=2023.816..2023.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"
"  CTE f"
"    ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)"
"  ->  Hash Join  (cost=55938.68..55942.07 rows=1 width=44) (actual time=2023.759..2023.780 rows=21 loops=1)"
"        Hash Cond: ((calendar.year = p.year) AND (calendar.week = p.week))"
"        ->  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"
"              ->  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"
"                    ->  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)))"
"        ->  Hash  (cost=55590.31..55590.31 rows=104 width=16) (actual time=2023.666..2023.666 rows=52 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"              ->  HashAggregate  (cost=55587.97..55589.27 rows=104 width=16) (actual time=2023.636..2023.650 rows=52 loops=1)"
"                    Group Key: p.year, p.week"
"                    ->  Nested Loop Left Join  (cost=396.69..54312.39 rows=63779 width=24) (actual time=6.944..1803.534 rows=447772 loops=1)"
"                          ->  Nested Loop  (cost=396.26..16919.17 rows=63779 width=22) (actual time=6.918..545.850 rows=447772 loops=1)"
"                                Join Filter: CASE WHEN (array_length(f.groups, 1) IS NULL) THEN true ELSE (p.group_key = ANY (f.groups)) END"
"                                ->  CTE Scan on f  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)"
"                                ->  Hash Join  (cost=396.26..13730.17 rows=127559 width=22) (actual time=6.912..411.414 rows=447772 loops=1)"
"                                      Hash Cond: ((st.store_key = sc.store_key) AND (p.group_key = sc.group_key))"
"                                      ->  Hash Join  (cost=12.26..12065.25 rows=243956 width=20) (actual time=0.063..244.761 rows=484744 loops=1)"
"                                            Hash Cond: (p.store_key = st.store_key)"
"                                            ->  Seq Scan on predictions_stores_groups_weekly p  (cost=0.00..10745.30 rows=484744 width=18) (actual time=0.004..86.170 rows=484744 loops=1)"
"                                                  Filter: (org_key = 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"
"                                                  ->  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"
"                                      ->  Hash  (cost=228.00..228.00 rows=10400 width=12) (actual time=6.828..6.828 rows=10400 loops=1)"
"                                            Buckets: 16384  Batches: 1  Memory Usage: 575kB"
"                                            ->  Seq Scan on stock_current_groups sc  (cost=0.00..228.00 rows=10400 width=12) (actual time=0.005..3.888 rows=10400 loops=1)"
"                                                  Filter: (org_key = 1)"
"                          ->  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=447772)"
"                                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"
"Execution Time: 2023.914 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.016 ms	0.0 %
Bitmap Index Scan	1	0.030 ms	0.0 %
CTE Scan	1	0.003 ms	0.0 %
Hash	3	2.969 ms	0.1 %
Hash Join	3	318.389 ms	15.7 %
HashAggregate	2	220.156 ms	10.9 %
Index Scan	1	895.544 ms	44.3 %
Nested Loop	1	134.433 ms	6.6 %
Nested Loop Left Join	1	362.140 ms	17.9 %
Result	1	0.001 ms	0.0 %
Seq Scan	3	90.100 ms	4.5 %
Sort	1	0.037 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
calendar	1	0.016 ms	0.0 %
Bitmap Heap Scan	1	0.016 ms	100.0 %
predictions_stores_groups_weekly	1	86.170 ms	4.3 %
Seq Scan	1	86.170 ms	100.0 %
stock_current_groups	1	3.888 ms	0.2 %
Seq Scan	1	3.888 ms	100.0 %
stores	1	0.042 ms	0.0 %
Seq Scan	1	0.042 ms	100.0 %
stores_groups_weekly	1	895.544 ms	44.3 %
Index Scan	1	895.544 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vbPs

Color mode:

Visible columns:

Per node type stats

Per table stats