Patch for "20.04_STABLE" branch: https://reviews.mahara.org/11907
-- 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: Fix Committed Status in Mahara 20.04 series: New Status in Mahara 20.10 series: New Status in Mahara 21.04 series: New Status in Mahara 21.10 series: Fix Committed 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
