explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5YEe

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 24.208 ↑ 1.0 1 1

Limit (cost=309.17..353.83 rows=1 width=96) (actual time=24.207..24.208 rows=1 loops=1)

2. 0.021 24.205 ↑ 1.0 1 1

WindowAgg (cost=309.17..353.83 rows=1 width=96) (actual time=24.204..24.205 rows=1 loops=1)

3. 0.166 24.184 ↑ 1.0 1 1

Seq Scan on userdata ud (cost=309.17..353.82 rows=1 width=96) (actual time=24.183..24.184 rows=1 loops=1)

  • Filter: ((NOT whitelist_entry) AND (email !~ '^(?:.*(?:ercom\.fr|cryptobox\.com))$'::text) AND ('can_invite_users'::text <> ALL (flags)) AND ('can_create_space'::text <> ALL (flags)) AND ('deleted'::text <> ALL (flags)) AND ('email_validated'::text = ANY (flags)) AND ((hashed SubPlan 1) OR (hashed SubPlan 4) OR (hashed SubPlan 5) OR (hashed SubPlan 6)))
  • Rows Removed by Filter: 6
4.          

SubPlan (forSeq Scan)

5. 0.329 0.867 ↓ 0.0 0 1

Hash Anti Join (cost=13.86..52.26 rows=210 width=32) (actual time=0.867..0.867 rows=0 loops=1)

  • Hash Cond: ((userdata.doc ->> 'mac_signing_certificate'::text) = sm.user_id)
6. 0.007 0.007 ↑ 60.0 7 1

Seq Scan on userdata (cost=0.00..14.20 rows=420 width=32) (actual time=0.004..0.007 rows=7 loops=1)

7. 0.246 0.531 ↓ 1.0 217 1

Hash (cost=11.16..11.16 rows=216 width=44) (actual time=0.531..0.531 rows=217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
8. 0.285 0.285 ↓ 1.0 217 1

Seq Scan on spacemember sm (cost=0.00..11.16 rows=216 width=44) (actual time=0.011..0.285 rows=217 loops=1)

9. 0.005 23.068 ↑ 1.0 6 1

HashAggregate (cost=174.41..174.47 rows=6 width=44) (actual time=23.065..23.068 rows=6 loops=1)

  • Group Key: sm2.user_id
10. 0.009 23.063 ↑ 1.0 6 1

HashAggregate (cost=174.33..174.39 rows=6 width=44) (actual time=23.060..23.063 rows=6 loops=1)

  • Group Key: sm2.user_id
11. 0.104 23.054 ↑ 3.7 6 1

Hash Semi Join (cost=161.12..174.28 rows=22 width=44) (actual time=22.954..23.054 rows=6 loops=1)

  • Hash Cond: ((sm2.user_id = sm3.user_id) AND (sm2.space_id = sm3.space_id) AND (sm2.id = (max(sm3.id))))
12. 0.036 0.036 ↓ 1.0 217 1

Seq Scan on spacemember sm2 (cost=0.00..11.16 rows=216 width=92) (actual time=0.005..0.036 rows=217 loops=1)

13. 0.015 22.914 ↑ 3.7 6 1

Hash (cost=160.74..160.74 rows=22 width=92) (actual time=22.914..22.914 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.008 22.899 ↑ 3.7 6 1

HashAggregate (cost=160.30..160.52 rows=22 width=92) (actual time=22.897..22.899 rows=6 loops=1)

  • Group Key: sm3.user_id, sm3.space_id
15. 0.073 22.891 ↑ 18.0 6 1

Hash Semi Join (cost=146.56..159.49 rows=108 width=92) (actual time=22.820..22.891 rows=6 loops=1)

  • Hash Cond: (sm3.space_id = spacemember.space_id)
16. 0.025 0.025 ↓ 1.0 217 1

Seq Scan on spacemember sm3 (cost=0.00..11.16 rows=216 width=92) (actual time=0.003..0.025 rows=217 loops=1)

17. 0.009 22.793 ↑ 1.3 6 1

Hash (cost=146.46..146.46 rows=8 width=44) (actual time=22.793..22.793 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.006 22.784 ↑ 1.3 6 1

HashAggregate (cost=146.30..146.38 rows=8 width=44) (actual time=22.783..22.784 rows=6 loops=1)

  • Group Key: spacemember.space_id
19. 0.976 22.778 ↑ 37.7 6 1

Hash Join (cost=113.35..145.74 rows=226 width=44) (actual time=21.785..22.778 rows=6 loops=1)

  • Hash Cond: (spacemember.user_id = (userdata_1.doc ->> 'mac_signing_certificate'::text))
  • Join Filter: (((spacemember.role = 'owner'::text) AND (hashed SubPlan 3) AND ('has_personal_space'::text = ANY (userdata_1.flags))) OR ('has_personal_space'::text <> ALL (userdata_1.flags)))
  • Rows Removed by Join Filter: 150
20. 0.138 21.456 ↓ 1.4 156 1

Seq Scan on spacemember (cost=33.65..45.35 rows=108 width=95) (actual time=21.354..21.456 rows=156 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 61
21.          

SubPlan (forSeq Scan)

22. 0.235 21.318 ↑ 1.0 1 1

HashAggregate (cost=33.64..33.65 rows=1 width=44) (actual time=21.318..21.318 rows=1 loops=1)

  • Group Key: m.user_id
23. 17.221 21.083 ↓ 360.0 360 1

Nested Loop (cost=0.00..33.64 rows=1 width=44) (actual time=0.134..21.083 rows=360 loops=1)

  • Join Filter: ((s.doc ->> 'author_id'::text) ~~ (m.space_user_id || '%'::text))
  • Rows Removed by Join Filter: 12660
24. 0.682 0.682 ↓ 60.0 60 1

Seq Scan on spacehistory s (cost=0.00..18.70 rows=1 width=1,082) (actual time=0.085..0.682 rows=60 loops=1)

  • Filter: (((public_metadata ->> 'name'::text) <> ''::text) AND ((public_metadata ->> 'status'::text) <> 'deleted'::text) AND (((public_metadata ->> 'usage'::text))::jsonb ? 'file_sharing'::text))
  • Rows Removed by Filter: 24
25. 3.180 3.180 ↓ 1.0 217 60

Seq Scan on spacemember m (cost=0.00..11.16 rows=216 width=88) (actual time=0.002..0.053 rows=217 loops=60)

26. 0.145 0.151 ↑ 60.0 7 1

Hash (cost=14.20..14.20 rows=420 width=64) (actual time=0.151..0.151 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.006 0.006 ↑ 60.0 7 1

Seq Scan on userdata userdata_1 (cost=0.00..14.20 rows=420 width=64) (actual time=0.004..0.006 rows=7 loops=1)

28.          

SubPlan (forHash Join)

29. 0.004 0.195 ↓ 3.5 7 1

Unique (cost=60.23..60.24 rows=2 width=1,082) (actual time=0.191..0.195 rows=7 loops=1)

30. 0.022 0.191 ↓ 3.5 7 1

Sort (cost=60.23..60.23 rows=2 width=1,082) (actual time=0.190..0.191 rows=7 loops=1)

  • Sort Key: ((sh.doc ->> 'space_id'::text))
  • Sort Method: quicksort Memory: 25kB
31. 0.084 0.169 ↓ 3.5 7 1

Append (cost=31.55..60.22 rows=2 width=1,082) (actual time=0.166..0.169 rows=7 loops=1)

32. 0.085 0.085 ↓ 0.0 0 1

HashAggregate (cost=31.55..31.56 rows=1 width=1,082) (actual time=0.085..0.085 rows=0 loops=1)

  • Group Key: (sh.doc ->> 'space_id'::text)
33. 0.000 0.083 ↓ 0.0 0 1

Nested Loop (cost=4.62..31.55 rows=1 width=1,082) (actual time=0.083..0.083 rows=0 loops=1)

34. 0.016 0.083 ↓ 0.0 0 1

Nested Loop (cost=4.34..28.60 rows=6 width=1,114) (actual time=0.083..0.083 rows=0 loops=1)

35. 0.039 0.039 ↓ 7.0 7 1

Index Scan using spacehistory_space_name_idx on spacehistory sh (cost=0.14..14.84 rows=1 width=1,082) (actual time=0.021..0.039 rows=7 loops=1)

  • Index Cond: ((public_metadata ->> 'name'::text) = ''::text)
  • Filter: (((public_metadata ->> 'usage'::text))::jsonb ? 'file_sharing'::text)
36. 0.014 0.028 ↓ 0.0 0 7

Bitmap Heap Scan on filerevision fr (cost=4.20..13.70 rows=6 width=32) (actual time=0.004..0.004 rows=0 loops=7)

  • Recheck Cond: ((doc ->> 'space_id'::text) = (sh.doc ->> 'space_id'::text))
37. 0.014 0.014 ↓ 0.0 0 7

Bitmap Index Scan on file_rev_idx (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=7)

  • Index Cond: ((doc ->> 'space_id'::text) = (sh.doc ->> 'space_id'::text))
38. 0.000 0.000 ↓ 0.0 0

Index Scan using dir_content_id_idx on dircontent dc (cost=0.28..0.48 rows=1 width=644) (never executed)

  • Index Cond: ((doc ->> '_id'::text) = (fr.doc ->> 'root_content_id'::text))
  • Filter: (((doc ->> 'type'::text) = 'directory'::text) AND ((doc ->> 'file_count'::text) = '0'::text))
39. 0.012 0.083 ↓ 7.0 7 1

HashAggregate (cost=28.62..28.63 rows=1 width=1,082) (actual time=0.080..0.083 rows=7 loops=1)

  • Group Key: (sh_1.doc ->> 'space_id'::text)
40. 0.026 0.071 ↓ 7.0 7 1

Nested Loop Left Join (cost=4.34..28.62 rows=1 width=1,082) (actual time=0.027..0.071 rows=7 loops=1)

  • Filter: ((fr_1.doc ->> 'space_id'::text) IS NULL)
41. 0.024 0.024 ↓ 7.0 7 1

Index Scan using spacehistory_space_name_idx on spacehistory sh_1 (cost=0.14..14.84 rows=1 width=1,082) (actual time=0.011..0.024 rows=7 loops=1)

  • Index Cond: ((public_metadata ->> 'name'::text) = ''::text)
  • Filter: (((public_metadata ->> 'usage'::text))::jsonb ? 'file_sharing'::text)
42. 0.007 0.021 ↓ 0.0 0 7

Bitmap Heap Scan on filerevision fr_1 (cost=4.20..13.70 rows=6 width=32) (actual time=0.003..0.003 rows=0 loops=7)

  • Recheck Cond: ((sh_1.doc ->> 'space_id'::text) = (doc ->> 'space_id'::text))
43. 0.014 0.014 ↓ 0.0 0 7

Bitmap Index Scan on file_rev_idx (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=7)

  • Index Cond: ((sh_1.doc ->> 'space_id'::text) = (doc ->> 'space_id'::text))
44. 0.000 0.000 ↓ 0.0 0

Hash Anti Join (cost=24.11..24.26 rows=1 width=44) (never executed)

  • Hash Cond: (spacemember_1.user_id = t.user_id)
45. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=11.70..11.76 rows=6 width=44) (never executed)

  • Group Key: spacemember_1.user_id
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on spacemember spacemember_1 (cost=0.00..11.16 rows=216 width=44) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.33..12.33 rows=7 width=44) (never executed)

48. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=12.19..12.26 rows=7 width=44) (never executed)

  • Group Key: t.user_id
49. 0.000 0.000 ↓ 0.0 0

Subquery Scan on t (cost=12.03..12.17 rows=7 width=44) (never executed)

50. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=12.03..12.10 rows=7 width=88) (never executed)

  • Group Key: spacemember_2.user_id, spacemember_2.space_id
51. 0.000 0.000 ↓ 0.0 0

Seq Scan on spacemember spacemember_2 (cost=0.00..11.70 rows=66 width=88) (never executed)

  • Filter: (role = 'owner'::text)
52. 0.000 0.000 ↓ 0.0 0

Unique (cost=31.97..57.64 rows=1 width=44) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=31.97..57.63 rows=1 width=44) (never executed)

  • Join Filter: ((s_1.doc ->> 'author_id'::text) ~~ (m_1.space_user_id || '%'::text))
54. 0.000 0.000 ↓ 0.0 0

Merge Semi Join (cost=31.97..34.21 rows=3 width=88) (never executed)

  • Merge Cond: ((m_1.user_id = spacemember_3.user_id) AND (m_1.space_id = spacemember_3.space_id) AND (m_1.id = (max(spacemember_3.id))))
55. 0.000 0.000 ↓ 0.0 0

Sort (cost=19.54..20.08 rows=216 width=136) (never executed)

  • Sort Key: m_1.user_id, m_1.space_id, m_1.id
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on spacemember m_1 (cost=0.00..11.16 rows=216 width=136) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Sort (cost=12.43..12.45 rows=7 width=92) (never executed)

  • Sort Key: spacemember_3.user_id, spacemember_3.space_id, (max(spacemember_3.id))
58. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=12.19..12.26 rows=7 width=92) (never executed)

  • Group Key: spacemember_3.user_id, spacemember_3.space_id
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on spacemember spacemember_3 (cost=0.00..11.70 rows=66 width=92) (never executed)

  • Filter: (role = 'owner'::text)