explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gcg9

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 0.345 ↑ 49.8 4 1

Merge Left Join (cost=368.20..382.62 rows=199 width=293) (actual time=0.329..0.345 rows=4 loops=1)

  • Merge Cond: (db.email = trustee.email)
2. 0.015 0.328 ↑ 49.8 4 1

Subquery Scan on db (cost=80.11..88.06 rows=199 width=237) (actual time=0.316..0.328 rows=4 loops=1)

  • Filter: ((db.status IS NOT NULL) AND (db.given_name ~~* '%%'::text) AND (db.surname ~~* '%%'::text) AND (CASE WHEN ('invitee'::text = ANY (db.flags)) THEN 'invitees'::text ELSE 'users'::text END ~~ '%'::text))
3. 0.003 0.313 ↑ 50.0 4 1

Unique (cost=80.11..82.06 rows=200 width=239) (actual time=0.310..0.313 rows=4 loops=1)

4. 0.010 0.310 ↑ 97.5 4 1

Sort (cost=80.11..81.09 rows=390 width=239) (actual time=0.309..0.310 rows=4 loops=1)

  • Sort Key: userdata.email, userdata.id DESC
  • Sort Method: quicksort Memory: 27kB
5. 0.090 0.300 ↑ 97.5 4 1

Hash Left Join (cost=30.78..63.33 rows=390 width=239) (actual time=0.251..0.300 rows=4 loops=1)

  • Hash Cond: ((userdata.doc ->> 'mac_signing_certificate'::text) = workspaces.user_id)
6. 0.023 0.023 ↑ 97.5 4 1

Seq Scan on userdata (cost=0.00..14.88 rows=390 width=170) (actual time=0.015..0.023 rows=4 loops=1)

  • Filter: (email ~~* '%%'::text)
7. 0.004 0.187 ↓ 2.0 2 1

Hash (cost=30.77..30.77 rows=1 width=40) (actual time=0.187..0.187 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.001 0.183 ↓ 2.0 2 1

Subquery Scan on workspaces (cost=30.74..30.77 rows=1 width=40) (actual time=0.181..0.183 rows=2 loops=1)

9. 0.006 0.182 ↓ 2.0 2 1

GroupAggregate (cost=30.74..30.76 rows=1 width=40) (actual time=0.180..0.182 rows=2 loops=1)

  • Group Key: sm4.user_id
10. 0.004 0.176 ↓ 4.0 4 1

Sort (cost=30.74..30.75 rows=1 width=64) (actual time=0.176..0.176 rows=4 loops=1)

  • Sort Key: sm4.user_id
  • Sort Method: quicksort Memory: 25kB
11. 0.011 0.172 ↓ 4.0 4 1

Nested Loop (cost=13.08..30.73 rows=1 width=64) (actual time=0.148..0.172 rows=4 loops=1)

  • Join Filter: ((((max(((spacehistory.doc ->> 'event_number'::text))::integer))::character varying))::text = (sh4.doc ->> 'event_number'::text))
  • Rows Removed by Join Filter: 2
12. 0.013 0.111 ↓ 1.2 10 1

Hash Join (cost=12.93..28.26 rows=8 width=160) (actual time=0.100..0.111 rows=10 loops=1)

  • Hash Cond: (sm4.space_id = ((spacehistory.doc ->> 'space_id'::text)))
13. 0.005 0.005 ↑ 42.0 10 1

Seq Scan on spacemember sm4 (cost=0.00..14.20 rows=420 width=96) (actual time=0.003..0.005 rows=10 loops=1)

14. 0.007 0.093 ↓ 1.5 6 1

Hash (cost=12.88..12.88 rows=4 width=64) (actual time=0.093..0.093 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.018 0.086 ↓ 1.5 6 1

GroupAggregate (cost=12.71..12.84 rows=4 width=64) (actual time=0.077..0.086 rows=6 loops=1)

  • Group Key: ((spacehistory.doc ->> 'space_id'::text))
16. 0.040 0.068 ↓ 2.0 8 1

Sort (cost=12.71..12.72 rows=4 width=64) (actual time=0.067..0.068 rows=8 loops=1)

  • Sort Key: ((spacehistory.doc ->> 'space_id'::text))
  • Sort Method: quicksort Memory: 41kB
17. 0.016 0.028 ↓ 2.0 8 1

Bitmap Heap Scan on spacehistory (cost=4.18..12.67 rows=4 width=64) (actual time=0.020..0.028 rows=8 loops=1)

  • Recheck Cond: ((doc ->> 'event_type'::text) = 'space_definition'::text)
  • Filter: ((doc ->> 'space_id'::text) IS NOT NULL)
  • Heap Blocks: exact=2
18. 0.012 0.012 ↓ 2.0 8 1

Bitmap Index Scan on spacehistory_event_type_idx (cost=0.00..4.18 rows=4 width=0) (actual time=0.012..0.012 rows=8 loops=1)

  • Index Cond: ((doc ->> 'event_type'::text) = 'space_definition'::text)
19. 0.050 0.050 ↑ 1.0 1 10

Index Scan using space_def_idx on spacehistory sh4 (cost=0.15..0.29 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (((doc ->> 'space_id'::text) = sm4.space_id) AND ((doc ->> 'event_id'::text) = sm4.space_definition_event_id))
  • Filter: (((public_metadata ->> 'status'::text) <> 'deleted'::text) AND ((public_metadata ->> 'usage'::text) = '["file_sharing", "messaging"]'::text))
  • Rows Removed by Filter: 0
20. 0.005 0.010 ↓ 0.0 0 1

Sort (cost=288.09..288.59 rows=200 width=56) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: trustee.email
  • Sort Method: quicksort Memory: 25kB
21. 0.000 0.005 ↓ 0.0 0 1

Subquery Scan on trustee (cost=276.44..280.44 rows=200 width=56) (actual time=0.005..0.005 rows=0 loops=1)

22. 0.002 0.005 ↓ 0.0 0 1

HashAggregate (cost=276.44..278.44 rows=200 width=88) (actual time=0.005..0.005 rows=0 loops=1)

  • Group Key: u.email
23. 0.000 0.003 ↓ 0.0 0 1

Hash Join (cost=82.07..260.82 rows=1,250 width=68) (actual time=0.003..0.003 rows=0 loops=1)

  • Hash Cond: ((deposit.doc ->> 'depositary_id'::text) = trustee_status.user_id)
24. 0.001 0.003 ↓ 0.0 0 1

Hash Join (cost=38.11..138.73 rows=1,250 width=64) (actual time=0.003..0.003 rows=0 loops=1)

  • Hash Cond: ((deposit.doc ->> 'user_id'::text) = u.user_id)
25. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on deposit (cost=0.00..22.50 rows=1,250 width=32) (actual time=0.002..0.002 rows=0 loops=1)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=35.61..35.61 rows=200 width=64) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Subquery Scan on u (cost=31.66..35.61 rows=200 width=64) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Unique (cost=31.66..33.61 rows=200 width=68) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Sort (cost=31.66..32.63 rows=390 width=68) (never executed)

  • Sort Key: userdata_1.email, userdata_1.id DESC
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on userdata userdata_1 (cost=0.00..14.88 rows=390 width=68) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Hash (cost=41.46..41.46 rows=200 width=36) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Subquery Scan on trustee_status (cost=37.51..41.46 rows=200 width=36) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Unique (cost=37.51..39.46 rows=200 width=72) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Sort (cost=37.51..38.48 rows=390 width=72) (never executed)

  • Sort Key: userdata_2.email, userdata_2.id DESC
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on userdata userdata_2 (cost=0.00..20.73 rows=390 width=72) (never executed)

Planning time : 3.609 ms
Execution time : 0.489 ms