explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8WUS

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 65,023.205 ↑ 1.0 1 1

Limit (cost=2,161,201.39..2,161,207.10 rows=1 width=39) (actual time=65,023.205..65,023.205 rows=1 loops=1)

2. 0.055 65,023.204 ↑ 32.0 1 1

WindowAgg (cost=2,161,201.39..2,161,384.26 rows=32 width=39) (actual time=65,023.204..65,023.204 rows=1 loops=1)

3. 4.707 65,023.149 ↓ 5.9 188 1

Seq Scan on userdata ud (cost=2,161,201.39..2,161,383.86 rows=32 width=39) (actual time=65,018.917..65,023.149 rows=188 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 ('has_personal_space'::text <> ALL (flags)) AND ('deleted'::text <> A
  • Rows Removed by Filter: 1095
4.          

SubPlan (forSeq Scan)

5. 15.329 36.433 ↑ 1.4 91 1

Nested Loop Anti Join (cost=0.42..980.77 rows=129 width=18) (actual time=0.161..36.433 rows=91 loops=1)

6. 0.576 0.576 ↑ 1.0 1,283 1

Seq Scan on userdata (cost=0.00..79.83 rows=1,283 width=18) (actual time=0.009..0.576 rows=1,283 loops=1)

7. 20.528 20.528 ↑ 30.0 1 1,283

Index Only Scan using spacemember_user_id_idx on spacemember sm (cost=0.42..4.70 rows=30 width=44) (actual time=0.016..0.016 rows=1 loops=1,283)

  • Index Cond: (user_id = (userdata.doc ->> 'mac_signing_certificate'::text))
  • Heap Fetches: 1192
8. 0.003 10.509 ↓ 0.0 0 1

HashAggregate (cost=19,581.10..19,592.64 rows=1,154 width=44) (actual time=10.509..10.509 rows=0 loops=1)

  • Group Key: sm2.user_id
9. 0.002 10.506 ↓ 0.0 0 1

HashAggregate (cost=19,566.67..19,578.21 rows=1,154 width=44) (actual time=10.506..10.506 rows=0 loops=1)

  • Group Key: sm2.user_id
10. 0.027 10.504 ↓ 0.0 0 1

Hash Join (cost=18,026.49..19,545.36 rows=8,526 width=44) (actual time=10.504..10.504 rows=0 loops=1)

  • Hash Cond: ((sm2.space_id = sm3.space_id) AND (sm2.id = (max(sm3.id))))
11. 0.002 0.002 ↑ 34,106.0 1 1

Seq Scan on spacemember sm2 (cost=0.00..1,263.06 rows=34,106 width=92) (actual time=0.002..0.002 rows=1 loops=1)

12. 0.001 10.475 ↓ 0.0 0 1

Hash (cost=17,986.55..17,986.55 rows=2,663 width=48) (actual time=10.475..10.475 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
13. 0.004 10.474 ↓ 0.0 0 1

HashAggregate (cost=17,933.29..17,959.92 rows=2,663 width=48) (actual time=10.474..10.474 rows=0 loops=1)

  • Group Key: sm3.space_id
14. 0.041 10.470 ↓ 0.0 0 1

Hash Semi Join (cost=16,030.74..17,762.76 rows=34,106 width=48) (actual time=10.470..10.470 rows=0 loops=1)

  • Hash Cond: (sm3.space_id = ((spacehistory_2.doc ->> 'space_id'::text)))
15. 0.002 0.002 ↑ 34,106.0 1 1

Seq Scan on spacemember sm3 (cost=0.00..1,263.06 rows=34,106 width=48) (actual time=0.002..0.002 rows=1 loops=1)

16. 0.000 10.427 ↓ 0.0 0 1

Hash (cost=15,953.82..15,953.82 rows=6,154 width=32) (actual time=10.427..10.427 rows=0 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 38kB
17. 0.001 10.427 ↓ 0.0 0 1

Unique (cost=15,861.51..15,892.28 rows=6,154 width=1,033) (actual time=10.427..10.427 rows=0 loops=1)

18. 0.007 10.426 ↓ 0.0 0 1

Sort (cost=15,861.51..15,876.89 rows=6,154 width=1,033) (actual time=10.426..10.426 rows=0 loops=1)

  • Sort Key: ((spacehistory_2.doc ->> 'space_id'::text))
  • Sort Method: quicksort Memory: 25kB
19. 6.156 10.419 ↓ 0.0 0 1

Seq Scan on spacehistory spacehistory_2 (cost=10,460.45..12,674.19 rows=6,154 width=1,033) (actual time=10.419..10.419 rows=0 loops=1)

  • Filter: (((((public_metadata ->> 'usage'::text))::jsonb ? 'messaging'::text) AND (NOT (((public_metadata ->> 'usage'::text))::jsonb ? 'file_sharing'::text))) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 12296
20.          

SubPlan (forSeq Scan)

21. 0.009 4.263 ↓ 0.0 0 1

Hash Join (cost=1,953.77..10,460.41 rows=15 width=1,033) (actual time=4.263..4.263 rows=0 loops=1)

  • Hash Cond: ((((spacehistory_1.doc ->> 'space_id'::text)) = (spacehistory.doc ->> 'space_id'::text)) AND ((max((spacehistory_1.doc ->> 'event_number'::text))) = (spacehistory.doc ->> 'event_number'::text)))
22. 0.012 0.023 ↑ 12,296.0 1 1

GroupAggregate (cost=0.41..7,953.51 rows=12,296 width=1,033) (actual time=0.023..0.023 rows=1 loops=1)

  • Group Key: (spacehistory_1.doc ->> 'space_id'::text)
23. 0.011 0.011 ↑ 4,098.7 3 1

Index Scan using spacehistory_space_id_idx on spacehistory spacehistory_1 (cost=0.41..7,707.59 rows=12,296 width=1,033) (actual time=0.008..0.011 rows=3 loops=1)

24. 0.001 4.231 ↓ 0.0 0 1

Hash (cost=1,952.44..1,952.44 rows=61 width=1,033) (actual time=4.231..4.231 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 4.230 4.230 ↓ 0.0 0 1

Seq Scan on spacehistory (cost=0.00..1,952.44 rows=61 width=1,033) (actual time=4.230..4.230 rows=0 loops=1)

  • Filter: ((public_metadata ->> 'status'::text) = 'deleted'::text)
  • Rows Removed by Filter: 12296
26. 0.879 64,971.500 ↓ 1.6 906 1

Subquery Scan on ui3 (cost=2,140,597.37..2,140,623.33 rows=577 width=44) (actual time=64,971.131..64,971.500 rows=906 loops=1)

  • Filter: (NOT (hashed SubPlan 4))
  • Rows Removed by Filter: 286
27. 7.228 10.567 ↓ 1.0 1,192 1

HashAggregate (cost=1,348.33..1,359.87 rows=1,154 width=44) (actual time=10.425..10.567 rows=1,192 loops=1)

  • Group Key: spacemember_2.user_id
28. 3.339 3.339 ↑ 1.0 34,106 1

Seq Scan on spacemember spacemember_2 (cost=0.00..1,263.06 rows=34,106 width=44) (actual time=0.002..3.339 rows=34,106 loops=1)

29.          

SubPlan (forSubquery Scan)

30. 155.315 64,960.054 ↑ 23.3 286 1

Nested Loop (cost=12,092.18..2,139,232.40 rows=6,659 width=44) (actual time=709.006..64,960.054 rows=286 loops=1)

  • Join Filter: ((array_agg(DISTINCT spacemember.space_id)) = uid2.array_agg)
  • Rows Removed by Join Filter: 1158338
31. 19.505 64,626.863 ↑ 1.2 972 1

GroupAggregate (cost=8,261.32..2,115,129.62 rows=1,154 width=88) (actual time=65.042..64,626.863 rows=972 loops=1)

  • Group Key: spacemember.user_id
32. 37,662.153 64,607.358 ↑ 59.4 8,822 1

Nested Loop (cost=8,261.32..2,112,494.15 rows=524,209 width=88) (actual time=37.877..64,607.358 rows=8,822 loops=1)

  • Join Filter: ((spacehistory_3.doc ->> 'author_id'::text) ~~ (spacemember.space_user_id || '%'::text))
  • Rows Removed by Join Filter: 108141304
33. 35.571 35.571 ↑ 1.0 34,106 1

Index Scan using spacemember_user_id_idx on spacemember (cost=0.41..5,336.95 rows=34,106 width=132) (actual time=0.006..35.571 rows=34,106 loops=1)

34. 26,867.205 26,909.634 ↓ 1.0 3,171 34,106

Materialize (cost=8,260.91..10,328.00 rows=3,074 width=1,033) (actual time=0.002..0.789 rows=3,171 loops=34,106)

35. 22.349 42.429 ↓ 1.0 3,171 1

Hash Join (cost=8,260.91..10,312.63 rows=3,074 width=1,033) (actual time=17.618..42.429 rows=3,171 loops=1)

  • Hash Cond: (((spacehistory_3.doc ->> 'space_id'::text) = ((spacehistory_4.doc ->> 'space_id'::text))) AND ((spacehistory_3.doc ->> 'event_number'::text) = (max((spacehistory_4.doc ->> 'event_number'::text)))))
36. 2.540 2.540 ↑ 1.0 12,296 1

Seq Scan on spacehistory spacehistory_3 (cost=0.00..1,890.96 rows=12,296 width=1,033) (actual time=0.001..2.540 rows=12,296 loops=1)

37. 1.081 17.540 ↑ 3.9 3,171 1

Hash (cost=8,076.47..8,076.47 rows=12,296 width=64) (actual time=17.540..17.540 rows=3,171 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 94kB
38. 7.585 16.459 ↑ 3.9 3,171 1

GroupAggregate (cost=0.41..7,953.51 rows=12,296 width=1,033) (actual time=0.017..16.459 rows=3,171 loops=1)

  • Group Key: (spacehistory_4.doc ->> 'space_id'::text)
39. 8.874 8.874 ↑ 1.0 12,296 1

Index Scan using spacehistory_space_id_idx on spacehistory spacehistory_4 (cost=0.41..7,707.59 rows=12,296 width=1,033) (actual time=0.007..8.874 rows=12,296 loops=1)

40. 55.696 177.876 ↓ 1.0 1,192 972

Materialize (cost=3,830.86..4,118.39 rows=1,154 width=32) (actual time=0.099..0.183 rows=1,192 loops=972)

41. 0.127 122.180 ↓ 1.0 1,192 1

Subquery Scan on uid2 (cost=3,830.86..4,112.62 rows=1,154 width=32) (actual time=95.609..122.180 rows=1,192 loops=1)

42. 20.377 122.053 ↓ 1.0 1,192 1

GroupAggregate (cost=3,830.86..4,101.08 rows=1,154 width=88) (actual time=95.609..122.053 rows=1,192 loops=1)

  • Group Key: spacemember_1.user_id
43. 96.594 101.676 ↑ 1.0 34,106 1

Sort (cost=3,830.86..3,916.12 rows=34,106 width=88) (actual time=95.587..101.676 rows=34,106 loops=1)

  • Sort Key: spacemember_1.user_id
  • Sort Method: external merge Disk: 3264kB
44. 5.082 5.082 ↑ 1.0 34,106 1

Seq Scan on spacemember spacemember_1 (cost=0.00..1,263.06 rows=34,106 width=88) (actual time=0.003..5.082 rows=34,106 loops=1)

Planning time : 6.297 ms
Execution time : 65,024.654 ms