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

That is from https://gitlab.com/groups/gitlab-org/-/issues?label_name%5B%5D=section%3A%3Adev&label_name%5B%5D=security&not%5Blabel_name%5D%5B%5D=xxx.

Related to #344074 (closed)

Screenshots or screen recordings

Screen_Shot_2021-10-28_at_10.48.17_AM

How to set up and validate locally

  1. In an issue list / board, add a NOT filter for a label that does not exist.
  2. 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.

Edited by Heinrich Lee Yu

Merge request reports

Loading