mahara-contributors team mailing list archive

[Doris Tam <1929473@xxxxxxxxxxxxxxxxxx>]

** Changed in: mahara/21.04
       Status: Fix Committed => Fix Released

-- 
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