explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Pd8

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.244 ↓ 2.0 6 1

Subquery Scan on members (cost=49.40..49.45 rows=3 width=165) (actual time=0.241..0.244 rows=6 loops=1)

2. 0.002 0.243 ↓ 2.0 6 1

Unique (cost=49.40..49.42 rows=3 width=169) (actual time=0.240..0.243 rows=6 loops=1)

3. 0.026 0.241 ↓ 2.7 8 1

Sort (cost=49.40..49.41 rows=3 width=169) (actual time=0.240..0.241 rows=8 loops=1)

  • Sort Key: members_1.user_id, members_1.invite_email, (CASE WHEN ((members_1.type)::text = 'ProjectMember'::text) THEN 1 WHEN ((members_1.type)::text = 'GroupMember'::text) THEN 2 ELSE 3 END)
  • Sort Method: quicksort Memory: 26kB
4. 0.001 0.215 ↓ 2.7 8 1

Result (cost=43.05..49.38 rows=3 width=169) (actual time=0.204..0.215 rows=8 loops=1)

5. 0.001 0.214 ↓ 2.7 8 1

Append (cost=43.05..49.33 rows=3 width=165) (actual time=0.203..0.214 rows=8 loops=1)

6. 0.024 0.211 ↓ 2.0 4 1

Hash Semi Join (cost=43.05..46.26 rows=2 width=165) (actual time=0.202..0.211 rows=4 loops=1)

  • Hash Cond: (members_1.id = members_2.id)
7. 0.015 0.015 ↓ 2.1 37 1

Seq Scan on members members_1 (cost=0.00..3.14 rows=18 width=165) (actual time=0.006..0.015 rows=37 loops=1)

  • Filter: ((invite_token IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 39
8. 0.001 0.172 ↑ 2.5 4 1

Hash (cost=42.92..42.92 rows=10 width=4) (actual time=0.172..0.172 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.000 0.171 ↑ 2.5 4 1

Append (cost=0.14..42.92 rows=10 width=4) (actual time=0.004..0.171 rows=4 loops=1)

10. 0.005 0.005 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_2 (cost=0.14..3.04 rows=1 width=4) (actual time=0.004..0.005 rows=4 loops=1)

  • Index Cond: ((source_id = 60) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
11. 0.025 0.166 ↓ 0.0 0 1

Hash Semi Join (cost=36.33..39.78 rows=9 width=4) (actual time=0.166..0.166 rows=0 loops=1)

  • Hash Cond: (members_3.source_id = namespaces.id)
12. 0.042 0.057 ↓ 2.1 19 1

Seq Scan on members members_3 (cost=5.21..8.54 rows=9 width=8) (actual time=0.044..0.057 rows=19 loops=1)

  • Filter: ((requested_at IS NULL) AND (NOT (hashed SubPlan 1)) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 57
13.          

SubPlan (forSeq Scan)

14. 0.008 0.015 ↓ 4.0 4 1

Nested Loop (cost=0.28..5.21 rows=1 width=4) (actual time=0.010..0.015 rows=4 loops=1)

15. 0.003 0.003 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_5 (cost=0.14..3.04 rows=1 width=4) (actual time=0.002..0.003 rows=4 loops=1)

  • Index Cond: ((source_id = 60) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
16. 0.004 0.004 ↑ 1.0 1 4

Index Only Scan using users_pkey on users (cost=0.14..2.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4)

  • Index Cond: (id = members_5.user_id)
  • Heap Fetches: 4
17. 0.001 0.084 ↑ 21.0 1 1

Hash (cost=30.85..30.85 rows=21 width=4) (actual time=0.084..0.084 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.083 0.083 ↑ 21.0 1 1

CTE Scan on base_and_ancestors namespaces (cost=30.22..30.64 rows=21 width=4) (actual time=0.055..0.083 rows=1 loops=1)

19.          

CTE base_and_ancestors

20. 0.051 0.081 ↑ 21.0 1 1

Recursive Union (cost=0.14..30.22 rows=21 width=352) (actual time=0.053..0.081 rows=1 loops=1)

21. 0.003 0.003 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.14..2.16 rows=1 width=352) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = 7)
  • Filter: ((type)::text = 'Group'::text)
22. 0.023 0.027 ↓ 0.0 0 1

Hash Join (cost=0.47..2.76 rows=2 width=352) (actual time=0.027..0.027 rows=0 loops=1)

  • Hash Cond: (namespaces_2.id = base_and_ancestors.parent_id)
23. 0.003 0.003 ↑ 13.0 1 1

Index Scan using index_namespaces_on_type on namespaces namespaces_2 (cost=0.14..2.37 rows=13 width=352) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
24. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.001 0.001 ↑ 10.0 1 1

WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.002 0.002 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_4 (cost=0.14..3.04 rows=1 width=165) (actual time=0.002..0.002 rows=4 loops=1)

  • Index Cond: ((source_id = 18) AND ((source_type)::text = 'Project'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'ProjectMember'::text))
Planning time : 2.116 ms