#	exclusive	inclusive	rows x	rows	loops	node
1.	1.659	59.725	↓ 1,111.0	6,666	1	→ Nested Loop (cost=1,651.79..3,360.76 rows=6 width=10) (actual time=22.823..59.725 rows=6,666 loops=1)
2.	1.576	31.402	↓ 12.1	6,666	1	→ Nested Loop (cost=1,651.35..2,430.13 rows=552 width=14) (actual time=22.802..31.402 rows=6,666 loops=1)
3.	1.533	23.160	↓ 12.1	1,111	1	→ HashAggregate (cost=1,650.93..1,651.85 rows=92 width=8) (actual time=22.780..23.160 rows=1,111 loops=1) Group Key: u0.id
4.	0.721	21.627	↓ 11.6	1,111	1	→ Nested Loop (cost=13.87..1,650.69 rows=96 width=8) (actual time=6.379..21.627 rows=1,111 loops=1)
5.	0.979	15.351	↓ 11.6	1,111	1	→ Nested Loop (cost=13.43..1,111.33 rows=96 width=12) (actual time=6.338..15.351 rows=1,111 loops=1)
6.	2.748	8.817	↓ 12.1	1,111	1	→ Bitmap Heap Scan on fs_node u0 (cost=13.01..342.39 rows=92 width=4) (actual time=6.304..8.817 rows=1,111 loops=1) Recheck Cond: (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery) Filter: (archived_at IS NULL) Rows Removed by Filter: 1 Heap Blocks: exact=912
7.	6.069	6.069	↓ 11.8	1,112	1	→ Bitmap Index Scan on path_gist_idx (cost=0.00..12.98 rows=94 width=0) (actual time=6.069..6.069 rows=1,112 loops=1) Index Cond: (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery)
8.	5.555	5.555	↑ 1.0	1	1,111	→ Index Scan using fs_permission_node_id_access_right_81ac3a0e_uniq on fs_permission u1 (cost=0.42..8.36 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,111) Index Cond: ((node_id = u0.id) AND ((access_right)::text = 'view'::text))
9.	5.555	5.555	↑ 1.0	1	1,111	→ Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission u2 (cost=0.43..5.62 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,111) Index Cond: ((user_id = 6) AND (permission_id = u1.id)) Heap Fetches: 1111
10.	6.666	6.666	↑ 1.0	6	1,111	→ Index Scan using fs_permission_node_id_638e8a2b on fs_permission (cost=0.42..8.40 rows=6 width=14) (actual time=0.004..0.006 rows=6 loops=1,111) Index Cond: (node_id = u0.id)
11.	26.664	26.664	↑ 1.0	1	6,666	→ Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission (cost=0.43..1.69 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6,666) Index Cond: ((user_id = 6) AND (permission_id = fs_permission.id)) Heap Fetches: 6666

Planning time	:	2.259 ms
Execution time	:	60.395 ms

Nested Loop  (cost=1651.79..3360.76 rows=6 width=10) (actual time=22.823..59.725 rows=6666 loops=1)
  ->  Nested Loop  (cost=1651.35..2430.13 rows=552 width=14) (actual time=22.802..31.402 rows=6666 loops=1)
        ->  HashAggregate  (cost=1650.93..1651.85 rows=92 width=8) (actual time=22.780..23.160 rows=1111 loops=1)
              Group Key: u0.id
              ->  Nested Loop  (cost=13.87..1650.69 rows=96 width=8) (actual time=6.379..21.627 rows=1111 loops=1)
                    ->  Nested Loop  (cost=13.43..1111.33 rows=96 width=12) (actual time=6.338..15.351 rows=1111 loops=1)
                          ->  Bitmap Heap Scan on fs_node u0  (cost=13.01..342.39 rows=92 width=4) (actual time=6.304..8.817 rows=1111 loops=1)
                                Recheck Cond: (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery)
                                Filter: (archived_at IS NULL)
                                Rows Removed by Filter: 1
                                Heap Blocks: exact=912
                                ->  Bitmap Index Scan on path_gist_idx  (cost=0.00..12.98 rows=94 width=0) (actual time=6.069..6.069 rows=1112 loops=1)
                                      Index Cond: (path ~ '1.402.403.5261.40005.40006.40009.44636.*{1}'::lquery)
                          ->  Index Scan using fs_permission_node_id_access_right_81ac3a0e_uniq on fs_permission u1  (cost=0.42..8.36 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1111)
                                Index Cond: ((node_id = u0.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.62 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1111)
                          Index Cond: ((user_id = 6) AND (permission_id = u1.id))
                          Heap Fetches: 1111
        ->  Index Scan using fs_permission_node_id_638e8a2b on fs_permission  (cost=0.42..8.40 rows=6 width=14) (actual time=0.004..0.006 rows=6 loops=1111)
              Index Cond: (node_id = u0.id)
  ->  Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission  (cost=0.43..1.69 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=6666)
        Index Cond: ((user_id = 6) AND (permission_id = fs_permission.id))
        Heap Fetches: 6666
Planning Time: 2.259 ms
Execution Time: 60.395 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	2.748 ms	4.6 %
Bitmap Index Scan	1	6.069 ms	10.2 %
HashAggregate	1	1.533 ms	2.6 %
Index Only Scan	2	32.219 ms	53.9 %
Index Scan	2	12.221 ms	20.5 %
Nested Loop	4	4.935 ms	8.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
fs_node	1	2.748 ms	4.6 %
Bitmap Heap Scan	1	2.748 ms	100.0 %
fs_permission	2	12.221 ms	20.5 %
Index Scan	2	12.221 ms	100.0 %
fs_userpermission	2	32.219 ms	53.9 %
Index Only Scan	2	32.219 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bGvm

Color mode:

Visible columns:

Per node type stats

Per table stats