explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vgl

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 2.433 ↓ 622.5 1,245 1

Append (cost=9.16..642.86 rows=2 width=491) (actual time=0.098..2.433 rows=1,245 loops=1)

2.          

CTE curr_usr

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.002..0.002 rows=1 loops=1)

4.          

CTE curr_container

5. 0.002 0.036 ↑ 1.0 1 1

Limit (cost=0.55..8.60 rows=1 width=33) (actual time=0.034..0.036 rows=1 loops=1)

6. 0.003 0.034 ↑ 1.0 1 1

Nested Loop (cost=0.55..8.60 rows=1 width=33) (actual time=0.034..0.034 rows=1 loops=1)

7. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on curr_usr u_2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

8. 0.031 0.031 ↑ 1.0 1 1

Index Scan using perm_idx_scope_url on perm p_2 (cost=0.55..8.57 rows=1 width=179) (actual time=0.031..0.031 rows=1 loops=1)

  • Index Cond: (lower(scope_url) = (lower(u_2.scope_url))::text)
  • Filter: (is_deleted AND ((u_2.owner_system)::text = owner_system))
9. 0.004 0.118 ↓ 2.0 2 1

Nested Loop (cost=0.55..81.45 rows=1 width=491) (actual time=0.097..0.118 rows=2 loops=1)

10. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on curr_usr u (cost=0.00..0.02 rows=1 width=96) (actual time=0.004..0.005 rows=1 loops=1)

11. 0.109 0.109 ↓ 2.0 2 1

Index Scan using perm_idx_scope_url on perm p (cost=0.55..81.42 rows=1 width=491) (actual time=0.089..0.109 rows=2 loops=1)

  • Index Cond: (lower(scope_url) = ANY (u.parent_scope_urls))
  • Filter: ((NOT is_deleted) AND (u.member_of && readers) AND ((u.owner_system)::text = owner_system))
12. 0.599 2.252 ↓ 1,243.0 1,243 1

Hash Join (cost=9.53..552.76 rows=1 width=491) (actual time=0.260..2.252 rows=1,243 loops=1)

  • Hash Cond: (p_1.owner_system = (u_1.owner_system)::text)
  • Join Filter: (p_1.readers && u_1.member_of)
13. 0.108 1.649 ↓ 8.9 1,243 1

Nested Loop (cost=9.50..551.33 rows=139 width=491) (actual time=0.221..1.649 rows=1,243 loops=1)

14. 0.038 0.038 ↑ 1.0 1 1

CTE Scan on curr_container c (cost=0.00..0.02 rows=1 width=32) (actual time=0.035..0.038 rows=1 loops=1)

15. 1.370 1.503 ↓ 8.9 1,243 1

Bitmap Heap Scan on perm p_1 (cost=9.50..549.92 rows=139 width=491) (actual time=0.184..1.503 rows=1,243 loops=1)

  • Recheck Cond: (container = c.container)
  • Filter: is_deleted
  • Heap Blocks: exact=525
16. 0.133 0.133 ↓ 8.9 1,243 1

Bitmap Index Scan on perm_idx_container (cost=0.00..9.47 rows=139 width=0) (actual time=0.133..0.133 rows=1,243 loops=1)

  • Index Cond: (container = c.container)
17. 0.003 0.004 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=0.003..0.004 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on curr_usr u_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1)

Planning time : 0.592 ms