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
** Changed in: mahara
Assignee: (unassigned) => Doris Tam (doristam)
** Changed in: mahara
Milestone: None => 21.10.0
** Changed in: mahara
Importance: Undecided => Medium
** Changed in: mahara
Status: New => Confirmed
--
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/1929473
Title:
Fix slow query for a student accessing the People page
Status in Mahara:
Confirmed
Bug description:
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)
...
To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1929473/+subscriptions
_______________________________________________
Mailing list: https://launchpad.net/~mahara-contributors
Post to : [email protected]
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
