mahara-contributors team mailing list archive

[Robert Lyon <1995532@xxxxxxxxxxxxxxxxxx>]

** Changed in: mahara
       Status: In Progress => Fix Committed

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