mahara-contributors team mailing list archive

[Kristina Hoeppner <1929473@xxxxxxxxxxxxxxxxxx>]

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