This item fixes the issue with the query run as a student when accessing the People page initially. Once loaded the page handled the data dynamically in the background without any issues.
--
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
--------------------------------------------------------------------------------------------------------------------------------------------
Finalize 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)
...
Hi Tim,
Thank you for reporting the problem and providing a patch. We'll add it into our code review system.
If you send me your email address, we can make sure to attribute the fix correctly. My email address should be visible in my Launchpad profile.
Thank you
Kristina