The initial query utilizes a Seq Scan on the 'usr' table with a nested subquery, resulting in a high execution time of 337495.376 ms. The optimized query, after the fix, introduces parallelism, employing a Parallel Hash Join and Partial Aggregate. This parallel approach significantly reduces the execution time to 29.719 ms. The fix eliminates unnecessary conditions, refines the JOIN operation, and optimizes the overall query plan for better performance. The introduction of parallel processing enhances efficiency, making the query more scalable and responsive to the specified conditions. Answer Credit https://blookets.org/
-- 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 Released Status in Mahara 20.04 series: Fix Released Status in Mahara 20.10 series: Fix Released Status in Mahara 21.04 series: Fix Released 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 : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
