summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDan McGee <dan@archlinux.org>2012-10-12 11:34:49 -0500
committerDan McGee <dan@archlinux.org>2012-10-12 11:35:28 -0500
commita71aa2e354599950f4bd464f0f19215f1c581141 (patch)
tree2d9ffa08831d69c6fbd5b30e96f0320e6babfbe0
parentf0b7e73de61c03a5018ed352605e6329611448d2 (diff)
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>
-rw-r--r--packages/utils.py11
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])