diff options
| author | Dan McGee <dan@archlinux.org> | 2012-10-12 18:34:49 +0200 |
|---|---|---|
| committer | Dan McGee <dan@archlinux.org> | 2012-10-12 18:35:28 +0200 |
| commit | a71aa2e354599950f4bd464f0f19215f1c581141 (patch) | |
| tree | 2d9ffa08831d69c6fbd5b30e96f0320e6babfbe0 /packages | |
| parent | f0b7e73de61c03a5018ed352605e6329611448d2 (diff) | |
| download | archweb-a71aa2e354599950f4bd464f0f19215f1c581141.tar.gz archweb-a71aa2e354599950f4bd464f0f19215f1c581141.tar.xz | |
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>
Diffstat (limited to 'packages')
| -rw-r--r-- | packages/utils.py | 11 |
1 files changed, 6 insertions, 5 deletions
diff --git a/packages/utils.py b/packages/utils.py index ee1b56b..c29e229 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]) |