Omit WHERE clause for NOT labels filter when empty
What does this MR do and why?
This clause will always be true when the labels provided are non-existent so we just remove it.
In certain cases (group issues list / boards), having this SQL clause results in PG picking a slow query plan.
Sample slow query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7086/commands/25059
Time: 28.574 s
- planning: 10.609 ms
- execution: 28.563 s
- I/O read: 2.312 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 42752481 (~326.20 GiB) from the buffer pool
- reads: 2783 (~21.70 MiB) from the OS file cache, including disk I/O
- dirtied: 196 (~1.50 MiB)
- writes: 0
With the useless clause removed: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7086/commands/25064
Time: 71.408 ms
- planning: 6.458 ms
- execution: 64.950 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 41736 (~326.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Related to #344074 (closed)
Screenshots or screen recordings
How to set up and validate locally
- In an issue list / board, add a NOT filter for a label that does not exist.
- Check the query in the performance bar.
Before:
SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964
AND ("issues"."state_id" IN (1))
AND NOT (EXISTS (SELECT "label_links".* FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" AND 1=0))
AND "issues"."issue_type" IN (0, 1)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 20 OFFSET 0
After:
SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964
AND ("issues"."state_id" IN (1))
AND "issues"."issue_type" IN (0, 1)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 20 OFFSET 0
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Heinrich Lee Yu
