#	exclusive	inclusive	rows x	rows	loops	node
1.	71.830	40,307.089	↓ 3,333.0	3,333	1	→ Nested Loop Semi Join (cost=2.01..26.21 rows=1 width=10) (actual time=281.084..40,307.089 rows=3,333 loops=1)
2.	94.685	319.123	↓ 93,262.0	93,262	1	→ Nested Loop (cost=0.86..16.89 rows=1 width=10) (actual time=0.062..319.123 rows=93,262 loops=1)
3.	37.914	37.914	↓ 93,262.0	93,262	1	→ Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission (cost=0.43..8.45 rows=1 width=4) (actual time=0.038..37.914 rows=93,262 loops=1) Index Cond: (user_id = 1234) Heap Fetches: 93262
4.	186.524	186.524	↑ 1.0	1	93,262	→ Index Scan using fs_permission_pkey on fs_permission (cost=0.42..8.44 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=93,262) Index Cond: (id = fs_userpermission.permission_id)
5.	8,856.095	39,916.136	↓ 0.0	0	93,262	→ Nested Loop (cost=1.15..9.30 rows=1 width=8) (actual time=0.428..0.428 rows=0 loops=93,262) Join Filter: (u1.id = u2.permission_id) Rows Removed by Join Filter: 1431
6.	0.000	279.786	↓ 0.0	0	93,262	→ Nested Loop (cost=0.72..0.84 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=93,262) Join Filter: (u0.id = u1.node_id)
7.	279.786	279.786	↓ 0.0	0	93,262	→ Index Scan using fs_node_pkey on fs_node u0 (cost=0.29..0.34 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=93,262) Index Cond: (id = fs_permission.node_id) Filter: ((archived_at IS NULL) AND (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery)) Rows Removed by Filter: 1
8.	13.332	13.332	↑ 1.0	1	3,333	→ Index Scan using fs_permission_node_id_access_right_81ac3a0e_uniq on fs_permission u1 (cost=0.42..0.48 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=3,333) Index Cond: ((node_id = fs_permission.node_id) AND ((access_right)::text = 'view'::text))
9.	30,780.255	30,780.255	↓ 40,038.0	40,038	3,333	→ Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission u2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.008..9.235 rows=40,038 loops=3,333) Index Cond: (user_id = 1234) Heap Fetches: 133446438

Planning time	:	2.257 ms
Execution time	:	40,307.738 ms

Nested Loop Semi Join  (cost=2.01..26.21 rows=1 width=10) (actual time=281.084..40307.089 rows=3333 loops=1)
  ->  Nested Loop  (cost=0.86..16.89 rows=1 width=10) (actual time=0.062..319.123 rows=93262 loops=1)
        ->  Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission  (cost=0.43..8.45 rows=1 width=4) (actual time=0.038..37.914 rows=93262 loops=1)
              Index Cond: (user_id = 1234)
              Heap Fetches: 93262
        ->  Index Scan using fs_permission_pkey on fs_permission  (cost=0.42..8.44 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=93262)
              Index Cond: (id = fs_userpermission.permission_id)
  ->  Nested Loop  (cost=1.15..9.30 rows=1 width=8) (actual time=0.428..0.428 rows=0 loops=93262)
        Join Filter: (u1.id = u2.permission_id)
        Rows Removed by Join Filter: 1431
        ->  Nested Loop  (cost=0.72..0.84 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=93262)
              Join Filter: (u0.id = u1.node_id)
              ->  Index Scan using fs_node_pkey on fs_node u0  (cost=0.29..0.34 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=93262)
                    Index Cond: (id = fs_permission.node_id)
                    Filter: ((archived_at IS NULL) AND (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery))
                    Rows Removed by Filter: 1
              ->  Index Scan using fs_permission_node_id_access_right_81ac3a0e_uniq on fs_permission u1  (cost=0.42..0.48 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=3333)
                    Index Cond: ((node_id = fs_permission.node_id) AND ((access_right)::text = 'view'::text))
        ->  Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission u2  (cost=0.43..8.45 rows=1 width=4) (actual time=0.008..9.235 rows=40038 loops=3333)
              Index Cond: (user_id = 1234)
              Heap Fetches: 133446438
Planning Time: 2.257 ms
Execution Time: 40307.738 ms

Per node type stats

node type	count	sum of times	% of query
Index Only Scan	2	30,818.169 ms	76.5 %
Index Scan	3	479.642 ms	1.2 %
Nested Loop	3	8,950.780 ms	22.2 %
Nested Loop Semi Join	1	71.830 ms	0.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
fs_node	1	279.786 ms	0.7 %
Index Scan	1	279.786 ms	100.0 %
fs_permission	2	199.856 ms	0.5 %
Index Scan	2	199.856 ms	100.0 %
fs_userpermission	2	30,818.169 ms	76.5 %
Index Only Scan	2	30,818.169 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ipQK

Color mode:

Visible columns:

Per node type stats

Per table stats