#	exclusive	inclusive	rows x	rows	loops	node
1.	55.844	1,111.446	↓ 136.0	136	1	→ Nested Loop (cost=3,529.60..10,764.85 rows=1 width=1,048) (actual time=187.485..1,111.446 rows=136 loops=1) Join Filter: (c.id = cu_1.channel_id) Rows Removed by Join Filter: 237008
2.						CTE cu
3.	29.668	44.196	↓ 1.0	48,305	1	→ Hash Join (cost=148.02..2,024.00 rows=48,294 width=45) (actual time=1.589..44.196 rows=48,305 loops=1) Hash Cond: (cm.user_id = u.id)
4.	12.956	12.956	↓ 1.0	48,305	1	→ Seq Scan on channel_members cm (cost=0.00..1,211.94 rows=48,294 width=32) (actual time=0.007..12.956 rows=48,305 loops=1)
5.	0.690	1.572	↑ 1.0	1,734	1	→ Hash (cost=126.34..126.34 rows=1,734 width=45) (actual time=1.572..1.572 rows=1,734 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 147kB
6.	0.882	0.882	↑ 1.0	1,734	1	→ Seq Scan on users u (cost=0.00..126.34 rows=1,734 width=45) (actual time=0.004..0.882 rows=1,734 loops=1)
7.	37.429	184.639	↓ 123.0	123	1	→ Hash Right Join (cost=1,505.60..7,651.23 rows=1 width=548) (actual time=184.529..184.639 rows=123 loops=1) Hash Cond: (m.channel_id = c.id) Filter: (m.inserted_at IS NULL) Rows Removed by Filter: 94864
8.	43.022	43.022	↑ 1.0	117,800	1	→ Seq Scan on messages m (cost=0.00..5,700.37 rows=118,537 width=24) (actual time=0.006..43.022 rows=117,800 loops=1)
9.	0.654	104.188	↓ 182.3	1,276	1	→ Hash (cost=1,505.52..1,505.52 rows=7 width=548) (actual time=104.188..104.188 rows=1,276 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 112kB
10.	2.698	103.534	↓ 182.3	1,276	1	→ Nested Loop (cost=0.29..1,505.52 rows=7 width=548) (actual time=1.603..103.534 rows=1,276 loops=1)
11.	79.942	79.942	↓ 174.1	10,447	1	→ CTE Scan on cu (cost=0.00..1,086.62 rows=60 width=532) (actual time=1.593..79.942 rows=10,447 loops=1) Filter: ((NOT dummy) AND onboarded AND ((role)::text = 'member'::text)) Rows Removed by Filter: 37858
12.	20.894	20.894	↓ 0.0	0	10,447	→ Index Scan using channels_pkey on channels c (cost=0.29..6.97 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=10,447) Index Cond: (id = cu.channel_id) Filter: (type = 'psychologist'::channel_type) Rows Removed by Filter: 1
13.	870.963	870.963	↓ 8.0	1,928	123	→ CTE Scan on cu cu_1 (cost=0.00..1,086.62 rows=241 width=532) (actual time=0.002..7.081 rows=1,928 loops=123) Filter: ((role)::text = 'psychologist'::text) Rows Removed by Filter: 46377

"Nested Loop  (cost=3529.60..10764.85 rows=1 width=1048) (actual time=187.485..1111.446 rows=136 loops=1)"
"  Join Filter: (c.id = cu_1.channel_id)"
"  Rows Removed by Join Filter: 237008"
"  CTE cu"
"    ->  Hash Join  (cost=148.02..2024.00 rows=48294 width=45) (actual time=1.589..44.196 rows=48305 loops=1)"
"          Hash Cond: (cm.user_id = u.id)"
"          ->  Seq Scan on channel_members cm  (cost=0.00..1211.94 rows=48294 width=32) (actual time=0.007..12.956 rows=48305 loops=1)"
"          ->  Hash  (cost=126.34..126.34 rows=1734 width=45) (actual time=1.572..1.572 rows=1734 loops=1)"
"                Buckets: 2048  Batches: 1  Memory Usage: 147kB"
"                ->  Seq Scan on users u  (cost=0.00..126.34 rows=1734 width=45) (actual time=0.004..0.882 rows=1734 loops=1)"
"  ->  Hash Right Join  (cost=1505.60..7651.23 rows=1 width=548) (actual time=184.529..184.639 rows=123 loops=1)"
"        Hash Cond: (m.channel_id = c.id)"
"        Filter: (m.inserted_at IS NULL)"
"        Rows Removed by Filter: 94864"
"        ->  Seq Scan on messages m  (cost=0.00..5700.37 rows=118537 width=24) (actual time=0.006..43.022 rows=117800 loops=1)"
"        ->  Hash  (cost=1505.52..1505.52 rows=7 width=548) (actual time=104.188..104.188 rows=1276 loops=1)"
"              Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 112kB"
"              ->  Nested Loop  (cost=0.29..1505.52 rows=7 width=548) (actual time=1.603..103.534 rows=1276 loops=1)"
"                    ->  CTE Scan on cu  (cost=0.00..1086.62 rows=60 width=532) (actual time=1.593..79.942 rows=10447 loops=1)"
"                          Filter: ((NOT dummy) AND onboarded AND ((role)::text = 'member'::text))"
"                          Rows Removed by Filter: 37858"
"                    ->  Index Scan using channels_pkey on channels c  (cost=0.29..6.97 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=10447)"
"                          Index Cond: (id = cu.channel_id)"
"                          Filter: (type = 'psychologist'::channel_type)"
"                          Rows Removed by Filter: 1"
"  ->  CTE Scan on cu cu_1  (cost=0.00..1086.62 rows=241 width=532) (actual time=0.002..7.081 rows=1928 loops=123)"
"        Filter: ((role)::text = 'psychologist'::text)"
"        Rows Removed by Filter: 46377"
"Planning time: 0.511 ms"
"Execution time: 1112.092 ms"

Per node type stats

node type	count	sum of times	% of query
CTE Scan	2	950.905 ms	85.6 %
Hash	2	1.344 ms	0.1 %
Hash Join	1	29.668 ms	2.7 %
Hash Right Join	1	37.429 ms	3.4 %
Index Scan	1	20.894 ms	1.9 %
Nested Loop	2	58.542 ms	5.3 %
Seq Scan	3	56.860 ms	5.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
channel_members	1	12.956 ms	1.2 %
Seq Scan	1	12.956 ms	100.0 %
channels	1	20.894 ms	1.9 %
Index Scan	1	20.894 ms	100.0 %
messages	1	43.022 ms	3.9 %
Seq Scan	1	43.022 ms	100.0 %
users	1	0.882 ms	0.1 %
Seq Scan	1	0.882 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UhsP

Color mode:

Visible columns:

Per node type stats

Per table stats