Public bug reported: 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) ** Affects: mahara Importance: Medium Status: In Progress ** Changed in: mahara Status: New => In Progress ** Changed in: mahara Importance: Undecided => Medium ** Changed in: mahara Milestone: None => 23.04.0 -- 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: In Progress 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 _______________________________________________ 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