mahara-contributors team mailing list archive

[Tim Lock <1870998@xxxxxxxxxxxxxxxxxx>]

This cause huge performance issues with large sites excluding yourself
twice for no reason.

Explain analyse SELECT COUNT(u.id) FROM usr u LEFT OUTER JOIN usr_account_preference h ON (u.id = h.usr AND h.field = 'hiderealname') WHERE u.id != 0 AND u.active = 1 AND u.deleted = 0 AND u.id != 43543 AND (u.id IN ( SELECT usr FROM usr_institution WHERE institution IN ('dev') AND u.id != 43543 ));
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=31657803.52..31657803.53 rows=1 width=8) (actual time=337495.337..337495.337 rows=1 loops=1)
   ->  Seq Scan on usr u  (cost=0.00..31657737.10 rows=26568 width=8) (actual time=8.908..337469.038 rows=47490 loops=1)
         Filter: ((id <> 0) AND (id <> 43543) AND (active = 1) AND (deleted = 0) AND (SubPlan 1))
         Rows Removed by Filter: 5831
         SubPlan 1
           ->  Result  (cost=0.00..1068.60 rows=47496 width=8) (actual time=0.002..4.988 rows=26305 loops=53226)
                 One-Time Filter: (u.id <> 43543)
                 ->  Seq Scan on usr_institution  (cost=0.00..1068.60 rows=47496 width=8) (actual time=0.001..3.047 rows=26305 loops=53226)
                       Filter: ((institution)::text = 'dev'::text)
                       Rows Removed by Filter: 5939
 Planning Time: 0.158 ms
 Execution Time: 337495.376 ms

With the fix:

Explain analyse SELECT COUNT(u.id) FROM usr u LEFT OUTER JOIN usr_account_preference h ON (u.id = h.usr AND h.field = 'hiderealname') WHERE u.id != 0 AND u.active = 1 AND u.deleted = 0 AND u.id != 43543 AND (u.id IN ( SELECT usr FROM usr_institution WHERE institution IN ('dev') ));
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=4188.30..4188.31 rows=1 width=8) (actual time=29.719..29.719 rows=1 loops=1)
   ->  Gather  (cost=4188.19..4188.30 rows=1 width=8) (actual time=29.607..31.436 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=3188.19..3188.20 rows=1 width=8) (actual time=26.441..26.441 rows=1 loops=2)
               ->  Parallel Hash Join  (cost=1083.22..3118.59 rows=27841 width=8) (actual time=8.881..25.216 rows=23745 loops=2)
                     Hash Cond: (u.id = usr_institution.usr)
                     ->  Parallel Seq Scan on usr u  (cost=0.00..1953.31 rows=31256 width=8) (actual time=0.021..10.581 rows=26613 loops=2)
                           Filter: ((id <> 0) AND (id <> 43543) AND (active = 1) AND (deleted = 0))
                           Rows Removed by Filter: 48
                     ->  Parallel Hash  (cost=835.85..835.85 rows=19790 width=8) (actual time=8.517..8.517 rows=23746 loops=2)
                           Buckets: 65536  Batches: 1  Memory Usage: 2400kB
                           ->  Parallel Index Only Scan using usrinst_usrins_pk on usr_institution  (cost=0.29..835.85 rows=19790 width=8) (actual time=0.043..4.285 rows=23746 loops=2)
                                 Index Cond: (institution = 'dev'::text)
                                 Heap Fetches: 149
 Planning Time: 0.435 ms
 Execution Time: 31.483 ms



** Patch added: "people-search-initial-load-nonadmin.patch"
   https://bugs.launchpad.net/mahara/+bug/1870998/+attachment/5498681/+files/people-search-initial-load-nonadmin.patch

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/1870998

Title:
  Can accidentally choose yourself when sharing a page when isolated
  institutions is on

Status in Mahara:
  Fix Released

Bug description:
  To replicate:

  1) Have a site with isolated institutions on
  2) Have a few members + 1 institution admin in an institution

  3) Login as normal member and share a page - selecting 'Person' option
  You should only see the other members of the institution (not yourself) 
  - this works as expected

  4) Login as institution admin and share a page - selecting 'Person' option
  You should only see the other members of the institution (not yourself) 
  - but you see yourself under 'Other people' part of the list

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1870998/+subscriptions