explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w6J9

Settings
# exclusive inclusive rows x rows loops node
1. 0.405 4.892 ↓ 1.5 1,426 1

Nested Loop (cost=319.53..617.70 rows=967 width=10) (actual time=2.253..4.892 rows=1,426 loops=1)

2.          

CTE children

3. 0.590 0.839 ↓ 1.1 26 1

Bitmap Heap Scan on fs_node (cost=4.48..92.82 rows=24 width=4) (actual time=0.274..0.839 rows=26 loops=1)

  • Recheck Cond: (path ~ '1.462.*{1}'::lquery)
  • Filter: (archived_at IS NULL)
  • Rows Removed by Filter: 21
  • Heap Blocks: exact=45
4. 0.249 0.249 ↓ 2.0 51 1

Bitmap Index Scan on path_gist_idx (cost=0.00..4.47 rows=26 width=0) (actual time=0.249..0.249 rows=51 loops=1)

  • Index Cond: (path ~ '1.462.*{1}'::lquery)
5.          

CTE view_permissions_node_ids

6. 0.018 1.983 ↓ 1.1 25 1

Nested Loop (cost=1.39..225.35 rows=23 width=4) (actual time=1.121..1.983 rows=25 loops=1)

7. 0.014 1.601 ↓ 1.1 26 1

Nested Loop (cost=0.96..203.36 rows=24 width=8) (actual time=1.015..1.601 rows=26 loops=1)

8. 0.029 0.885 ↓ 1.1 26 1

HashAggregate (cost=0.54..0.78 rows=24 width=4) (actual time=0.876..0.885 rows=26 loops=1)

  • Group Key: children.id
9. 0.856 0.856 ↓ 1.1 26 1

CTE Scan on children (cost=0.00..0.48 rows=24 width=4) (actual time=0.277..0.856 rows=26 loops=1)

10. 0.702 0.702 ↑ 1.0 1 26

Index Scan using fs_permission_node_id_access_right_81ac3a0e_uniq on fs_permission fs_permission_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=26)

  • Index Cond: ((node_id = children.id) AND ((access_right)::text = 'view'::text))
11. 0.364 0.364 ↑ 1.0 1 26

Index Only Scan using fs_userpermission_user_id_permission_id_baa0e460_uniq on fs_userpermission fs_userpermission_1 (cost=0.43..0.92 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=26)

  • Index Cond: ((user_id = 178) AND (permission_id = fs_permission_1.id))
  • Heap Fetches: 6
12. 0.053 2.537 ↓ 1.1 150 1

Nested Loop (cost=0.94..198.78 rows=139 width=14) (actual time=2.082..2.537 rows=150 loops=1)

13. 0.036 2.034 ↓ 1.1 25 1

HashAggregate (cost=0.52..0.75 rows=23 width=4) (actual time=2.017..2.034 rows=25 loops=1)

  • Group Key: view_permissions_node_ids.node_id
14. 1.998 1.998 ↓ 1.1 25 1

CTE Scan on view_permissions_node_ids (cost=0.00..0.46 rows=23 width=4) (actual time=1.123..1.998 rows=25 loops=1)

15. 0.450 0.450 ↑ 1.0 6 25

Index Scan using fs_permission_node_id_638e8a2b on fs_permission (cost=0.42..8.55 rows=6 width=14) (actual time=0.015..0.018 rows=6 loops=25)

  • Index Cond: (node_id = view_permissions_node_ids.node_id)
16. 1.950 1.950 ↓ 1.2 10 150

Index Only Scan using fs_userpermission_permission_id_5755f64c on fs_userpermission (cost=0.43..0.64 rows=8 width=4) (actual time=0.010..0.013 rows=10 loops=150)

  • Index Cond: (permission_id = fs_permission.id)
  • Heap Fetches: 334
Planning time : 1.063 ms
Execution time : 5.206 ms