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
