#	exclusive	inclusive	rows x	rows	loops	node
1.	136.851	1,533.100	↓ 9,999.0	9,999	1	→ Nested Loop Semi Join (cost=2.01..27.43 rows=1 width=34) (actual time=96.494..1,533.100 rows=9,999 loops=1)
2.	193.487	716.143	↓ 226,702.0	226,702	1	→ Nested Loop (cost=0.86..21.34 rows=1 width=34) (actual time=0.059..716.143 rows=226,702 loops=1)
3.	69.252	69.252	↓ 226,702.0	226,702	1	→ Index Scan using fs_userpermission_user_id_541f14f8 on fs_userpermission (cost=0.43..12.89 rows=1 width=20) (actual time=0.035..69.252 rows=226,702 loops=1) Index Cond: (user_id = ANY ('{1234,1239}'::integer[]))
4.	453.404	453.404	↑ 1.0	1	226,702	→ 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=226,702) Index Cond: (id = fs_userpermission.permission_id)
5.	0.000	680.106	↓ 0.0	0	226,702	→ Nested Loop (cost=1.15..6.08 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=226,702)
6.	196.705	680.106	↓ 0.0	0	226,702	→ Nested Loop (cost=0.72..0.84 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=226,702) Join Filter: (u0.id = u1.node_id)
7.	453.404	453.404	↓ 0.0	0	226,702	→ Index Scan using fs_node_pkey on fs_node u0 (cost=0.29..0.34 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=226,702) 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.	29.997	29.997	↑ 1.0	1	9,999	→ 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.003..0.003 rows=1 loops=9,999) Index Cond: ((node_id = fs_permission.node_id) AND ((access_right)::text = 'view'::text))
9.	29.997	29.997	↑ 1.0	1	9,999	→ Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission u2 (cost=0.43..5.23 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=9,999) Index Cond: ((user_id = ANY ('{1234,1239}'::integer[])) AND (permission_id = u1.id)) Heap Fetches: 9999

Planning time	:	2.220 ms
Execution time	:	1,534.107 ms

Nested Loop Semi Join  (cost=2.01..27.43 rows=1 width=34) (actual time=96.494..1533.100 rows=9999 loops=1)
  ->  Nested Loop  (cost=0.86..21.34 rows=1 width=34) (actual time=0.059..716.143 rows=226702 loops=1)
        ->  Index Scan using fs_userpermission_user_id_541f14f8 on fs_userpermission  (cost=0.43..12.89 rows=1 width=20) (actual time=0.035..69.252 rows=226702 loops=1)
              Index Cond: (user_id = ANY ('{1234,1239}'::integer[]))
        ->  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=226702)
              Index Cond: (id = fs_userpermission.permission_id)
  ->  Nested Loop  (cost=1.15..6.08 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=226702)
        ->  Nested Loop  (cost=0.72..0.84 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=226702)
              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.002..0.002 rows=0 loops=226702)
                    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.003..0.003 rows=1 loops=9999)
                    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..5.23 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=9999)
              Index Cond: ((user_id = ANY ('{1234,1239}'::integer[])) AND (permission_id = u1.id))
              Heap Fetches: 9999
Planning Time: 2.220 ms
Execution Time: 1534.107 ms

Per node type stats

node type	count	sum of times	% of query
Index Only Scan	1	29.997 ms	2.0 %
Index Scan	4	1,006.057 ms	65.6 %
Nested Loop	3	390.192 ms	25.5 %
Nested Loop Semi Join	1	136.851 ms	8.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
fs_node	1	453.404 ms	29.6 %
Index Scan	1	453.404 ms	100.0 %
fs_permission	2	483.401 ms	31.5 %
Index Scan	2	483.401 ms	100.0 %
fs_userpermission	2	99.249 ms	6.5 %
Index Only Scan	1	29.997 ms	30.2 %
Index Scan	1	69.252 ms	69.8 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DIxT

Color mode:

Visible columns:

Per node type stats

Per table stats