mahara-contributors team mailing list archive

[Mahara Bot <1995532@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://reviews.mahara.org/c/mahara/+/13423
Committed: https://git.mahara.org/mahara/mahara/commit/818aff33dcfa49af60031725f5bbb59245344f2c
Submitter: "Robert Lyon <robertl@xxxxxxxxxxxxxxx>"
Branch:    main

commit 818aff33dcfa49af60031725f5bbb59245344f2c
Author: Robert Lyon <robertl@xxxxxxxxxxxxxxx>
Date:   Thu Nov 3 10:48:08 2022 +1300

Bug 1995532: Make the 'new View($id)' call slightly faster

By adjusting the join / where statement so we only do 1 index scan

Change-Id: I2a1aeb1eb8ffb1c6cb49978894af3aa203308a65
Signed-off-by: Robert Lyon <robertl@xxxxxxxxxxxxxxx>

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

Title:
  Adjust the new View() construct by integer for ID call

Status in Mahara:
  Fix Committed

Bug description:
  We can make the sql query more efficient by doing the filter on the
  join rather after the join results.

  Current way SQL explain output:

  explain analyze select v.id from view v left join "group" g on v.group = g.id where v.id =12 and (v.group is null or g.deleted = 0);
                                                          QUERY PLAN                                                        
  --------------------------------------------------------------------------------------------------------------------------
   Nested Loop Left Join  (cost=0.71..16.75 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)
     Filter: ((v."group" IS NULL) OR (g.deleted = 0))
     ->  Index Scan using view_id_pk on view v  (cost=0.42..8.44 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=1)
           Index Cond: (id = 12)
     ->  Index Scan using grou_id_pk on "group" g  (cost=0.29..8.30 rows=1 width=10) (never executed)
           Index Cond: (v."group" = id)
   Planning time: 0.652 ms
   Execution time: 0.107 ms

  
  New way SQL explain output:

  explain analyze select v.id from view v left join "group" g on v.group = g.id and (v.group is null or g.deleted = 0) where v.id = 12;
                                                       QUERY PLAN                                                     
  --------------------------------------------------------------------------------------------------------------------
   Index Scan using view_id_pk on view v  (cost=0.42..8.44 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=1)
     Index Cond: (id = 12)
   Planning time: 0.272 ms
   Execution time: 0.048 ms
  (4 rows)

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1995532/+subscriptions