explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D27U

Settings
# exclusive inclusive rows x rows loops node
1. 9.166 1,629.955 ↓ 1.0 5,180 1

Merge Left Join (cost=11,053.11..11,479.08 rows=5,150 width=302) (actual time=511.422..1,629.955 rows=5,180 loops=1)

  • Merge Cond: (db.email = trustee.email)
2. 726.396 1,376.752 ↓ 1.0 5,180 1

Nested Loop Left Join (cost=7,178.43..7,436.89 rows=5,149 width=246) (actual time=267.381..1,376.752 rows=5,180 loops=1)

  • Join Filter: (db.mac_signing_certificate = workspaces.user_id)
  • Rows Removed by Join Filter: 5,065,129
3. 16.170 158.256 ↓ 1.0 5,180 1

Subquery Scan on db (cost=793.86..975.05 rows=5,149 width=238) (actual time=136.575..158.256 rows=5,180 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))
4. 4.391 142.086 ↓ 1.0 5,180 1

Unique (cost=793.86..819.77 rows=5,176 width=240) (actual time=136.552..142.086 rows=5,180 loops=1)

5. 64.706 137.695 ↓ 1.0 5,187 1

Sort (cost=793.86..806.82 rows=5,183 width=240) (actual time=136.549..137.695 rows=5,187 loops=1)

  • Sort Key: userdata.email, userdata.id DESC
  • Sort Method: quicksort Memory: 2,071kB
6. 72.989 72.989 ↓ 1.0 5,187 1

Seq Scan on userdata (cost=0.00..474.08 rows=5,183 width=240) (actual time=0.089..72.989 rows=5,187 loops=1)

  • Filter: (email ~~* '%%'::text)
7. 362.116 492.100 ↓ 978.0 978 5,180

Materialize (cost=6,384.57..6,384.61 rows=1 width=52) (actual time=0.025..0.095 rows=978 loops=5,180)

8. 0.186 129.984 ↓ 1,093.0 1,093 1

Subquery Scan on workspaces (cost=6,384.57..6,384.60 rows=1 width=52) (actual time=127.300..129.984 rows=1,093 loops=1)

9. 1.914 129.798 ↓ 1,093.0 1,093 1

GroupAggregate (cost=6,384.57..6,384.59 rows=1 width=52) (actual time=127.298..129.798 rows=1,093 loops=1)

  • Group Key: sm4.user_id
10. 12.195 127.884 ↓ 5,606.0 5,606 1

Sort (cost=6,384.57..6,384.58 rows=1 width=88) (actual time=127.284..127.884 rows=5,606 loops=1)

  • Sort Key: sm4.user_id
  • Sort Method: quicksort Memory: 981kB
11. 3.241 115.689 ↓ 5,606.0 5,606 1

Nested Loop (cost=6,129.68..6,384.56 rows=1 width=88) (actual time=58.750..115.689 rows=5,606 loops=1)

  • Join Filter: (((spacehistory.doc ->> 'space_id'::text)) = sm4.space_id)
12. 10.222 74.669 ↓ 5,397.0 5,397 1

Hash Join (cost=6,129.39..6,377.85 rows=1 width=889) (actual time=58.725..74.669 rows=5,397 loops=1)

  • Hash Cond: ((((spacehistory.doc ->> 'space_id'::text)) = (sh4.doc ->> 'space_id'::text)) AND ((((max(((spacehistory.doc ->> 'event_number'::text))::integer))::character varying))::text = (sh4.doc ->> 'event_number'::text)))
13. 13.822 32.495 ↓ 1.2 8,727 1

HashAggregate (cost=3,109.62..3,241.38 rows=7,529 width=64) (actual time=26.756..32.495 rows=8,727 loops=1)

  • Group Key: (spacehistory.doc ->> 'space_id'::text)
14. 18.673 18.673 ↓ 1.0 9,510 1

Seq Scan on spacehistory (cost=0.00..2,990.95 rows=9,494 width=889) (actual time=0.016..18.673 rows=9,510 loops=1)

  • Filter: (((doc ->> 'space_id'::text) IS NOT NULL) AND ((doc ->> 'event_type'::text) = 'space_definition'::text))
  • Rows Removed by Filter: 10,827
15. 16.124 31.952 ↓ 57.6 5,878 1

Hash (cost=3,018.24..3,018.24 rows=102 width=857) (actual time=31.952..31.952 rows=5,878 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7,459kB
16. 15.828 15.828 ↓ 57.6 5,878 1

Seq Scan on spacehistory sh4 (cost=0.00..3,018.24 rows=102 width=857) (actual time=0.023..15.828 rows=5,878 loops=1)

  • Filter: (((public_metadata ->> 'status'::text) <> 'deleted'::text) AND ((public_metadata ->> 'usage'::text) = '["file_sharing", "messaging"]'::text))
  • Rows Removed by Filter: 14,459
17. 37.779 37.779 ↑ 1.0 1 5,397

Index Scan using spacemember_space_definition_event_id_idx on spacemember sm4 (cost=0.29..6.70 rows=1 width=132) (actual time=0.006..0.007 rows=1 loops=5,397)

  • Index Cond: (space_definition_event_id = (sh4.doc ->> 'event_id'::text))
  • Filter: ((sh4.doc ->> 'space_id'::text) = space_id)
18. 0.146 244.037 ↑ 120.4 43 1

Sort (cost=3,874.68..3,887.62 rows=5,177 width=60) (actual time=244.027..244.037 rows=43 loops=1)

  • Sort Key: trustee.email
  • Sort Method: quicksort Memory: 28kB
19. 0.000 243.891 ↑ 120.4 43 1

Subquery Scan on trustee (cost=3,451.78..3,555.32 rows=5,177 width=60) (actual time=243.832..243.891 rows=43 loops=1)

20. 0.200 243.882 ↑ 120.4 43 1

HashAggregate (cost=3,451.78..3,503.55 rows=5,177 width=96) (actual time=243.830..243.882 rows=43 loops=1)

  • Group Key: userdata_2.email
21. 1.113 243.682 ↑ 597.5 120 1

Hash Join (cost=1,411.37..2,555.51 rows=71,701 width=862) (actual time=240.502..243.682 rows=120 loops=1)

  • Hash Cond: (((userdata_1.doc ->> 'mac_signing_certificate'::text)) = (deposit.doc ->> 'depositary_id'::text))
22. 1.581 126.255 ↓ 1.0 5,180 1

Unique (cost=651.41..677.33 rows=5,177 width=76) (actual time=124.154..126.255 rows=5,180 loops=1)

23. 66.769 124.674 ↓ 1.0 5,187 1

Sort (cost=651.41..664.37 rows=5,184 width=76) (actual time=124.152..124.674 rows=5,187 loops=1)

  • Sort Key: userdata_1.email, userdata_1.id DESC
  • Sort Method: quicksort Memory: 922kB
24. 57.905 57.905 ↓ 1.0 5,187 1

Seq Scan on userdata userdata_1 (cost=0.00..331.56 rows=5,184 width=76) (actual time=0.050..57.905 rows=5,187 loops=1)

25. 0.202 116.314 ↑ 23.1 120 1

Hash (cost=725.33..725.33 rows=2,770 width=858) (actual time=116.314..116.314 rows=120 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 136kB
26. 1.116 116.112 ↑ 23.1 120 1

Hash Join (cost=594.06..725.33 rows=2,770 width=858) (actual time=113.006..116.112 rows=120 loops=1)

  • Hash Cond: (((userdata_2.doc ->> 'mac_signing_certificate'::text)) = (deposit.doc ->> 'user_id'::text))
27. 1.500 114.696 ↓ 1.0 5,180 1

Unique (cost=573.65..599.57 rows=5,177 width=72) (actual time=112.684..114.696 rows=5,180 loops=1)

28. 64.265 113.196 ↓ 1.0 5,187 1

Sort (cost=573.65..586.61 rows=5,184 width=72) (actual time=112.681..113.196 rows=5,187 loops=1)

  • Sort Key: userdata_2.email, userdata_2.id DESC
  • Sort Method: quicksort Memory: 922kB
29. 48.931 48.931 ↓ 1.0 5,187 1

Seq Scan on userdata userdata_2 (cost=0.00..253.80 rows=5,184 width=72) (actual time=0.042..48.931 rows=5,187 loops=1)

30. 0.300 0.300 ↓ 1.1 122 1

Hash (cost=19.07..19.07 rows=107 width=822) (actual time=0.300..0.300 rows=122 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 110kB
31. 0.111 0.111 ↓ 1.1 122 1

Seq Scan on deposit (cost=0.00..19.07 rows=107 width=822) (actual time=0.022..0.111 rows=122 loops=1)

Planning time : 3.630 ms
Execution time : 1,631.150 ms