diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2015-11-11 12:50:36 +0100 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2015-11-19 11:58:05 +0100 |
commit | 8591cc02be6b12ed60f763a5e0147f2cbbca99e1 (patch) | |
tree | 01928946005fb65bc7908fb69b7fb00c33ab0d2b /scripts | |
parent | e9cd58f5d50a7b5cfc14e08cd9526505e24f1071 (diff) | |
download | gitlab-ce-8591cc02be6b12ed60f763a5e0147f2cbbca99e1.tar.gz |
Use a JOIN in IssuableFinder#by_project
When using IssuableFinder/IssuesFinder to find issues for multiple
projects it's more efficient to use a JOIN + a "WHERE project_id IN"
condition opposed to running a sub-query.
This change means that when finding issues without labels we're now
using the following SQL:
SELECT issues.*
FROM issues
JOIN projects ON projects.id = issues.project_id
LEFT JOIN label_links ON label_links.target_type = 'Issue'
AND label_links.target_id = issues.id
WHERE (
projects.id IN (...)
OR projects.visibility_level IN (20, 10)
)
AND issues.state IN ('opened','reopened')
AND label_links.id IS NULL
ORDER BY issues.id DESC;
instead of:
SELECT issues.*
FROM issues
LEFT JOIN label_links ON label_links.target_type = 'Issue'
AND label_links.target_id = issues.id
WHERE issues.project_id IN (
SELECT id
FROM projects
WHERE id IN (...)
OR visibility_level IN (20,10)
)
AND issues.state IN ('opened','reopened')
AND label_links.id IS NULL
ORDER BY issues.id DESC;
The big benefit here is that in the last case PostgreSQL can't properly
use all available indexes. In particular it ends up performing a
sequence scan on the "label_links" table (processing around 290 000
rows). The new query is roughly 2x as fast as the old query.
Diffstat (limited to 'scripts')
0 files changed, 0 insertions, 0 deletions