Make wrong permissions query more efficient

This removes the subplan and per-row query in favor of a LEFT JOIN where
we look for non-matching rows. Tested in sqlite3 and PostgreSQL.

Signed-off-by: Dan McGee <dan@archlinux.org>

1 files changed, 6 insertions, 5 deletions

diff --git a/packages/utils.py b/packages/utils.py
index ee1b56b3..c29e2297 100644
--- a/packages/utils.py
+++ b/packages/utils.py
@@ -228,12 +228,13 @@ SELECT DISTINCT id
         FROM packages p
         JOIN packages_packagerelation pr ON p.pkgbase = pr.pkgbase
         WHERE pr.type = %s
-        ) pkgs
-    WHERE pkgs.repo_id NOT IN (
-        SELECT repo_id FROM user_profiles_allowed_repos ar
+    ) mp
+    LEFT JOIN (
+        SELECT user_id, repo_id FROM user_profiles_allowed_repos ar
         INNER JOIN user_profiles up ON ar.userprofile_id = up.id
-        WHERE up.user_id = pkgs.user_id
-    )
+    ) ur
+    ON mp.user_id = ur.user_id AND mp.repo_id = ur.repo_id
+    WHERE ur.user_id IS NULL;
 """
     cursor = connection.cursor()
     cursor.execute(sql, [PackageRelation.MAINTAINER])