Skip to content

Add negated params filter to epics search

What does this MR do?

#212579 (closed)

This MR adds the NOT (!=) operator to epics search. The operator is available for labels and author params.

Screen_Shot_2020-05-15_at_11.16.06

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Query Plans

Labels Positive
SELECT "epics".*
FROM "epics"
  INNER JOIN "label_links" ON "label_links"."target_id" = "epics"."id"
    AND "label_links"."target_type" = 1
    INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
WHERE
    "epics"."group_id" = 2
    AND "labels"."title" IN (3, 4)
GROUP BY
    "epics"."id"
HAVING (COUNT(DISTINCT labels.title) = 2)
ORDER BY
    "epics"."id" DESC
LIMIT 20 OFFSET 0
Labels Negative
SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" = 1
  AND NOT (EXISTS (
            SELECT
                true
            FROM
                "label_links"
                INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
            WHERE
                "label_links"."target_type" = 'Epic'
                AND "label_links"."target_id" = "epics"."id"
                AND "labels"."title" IN ('label1', 'label2')))
ORDER BY
    "epics"."id" DESC
LIMIT 20 OFFSET 0
 Limit  (cost=40.83..40.83 rows=3 width=1253) (actual time=1.917..1.917 rows=0 loops=1)
   Buffers: shared hit=3 read=2
   I/O Timings: read=1.793
   ->  Sort  (cost=40.83..40.83 rows=3 width=1253) (actual time=1.916..1.916 rows=0 loops=1)
         Sort Key: epics.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=2
         I/O Timings: read=1.
Time: 2.999 ms
  - planning: 0.980 ms
  - execution: 2.019 ms
    - I/O read: 1.793 ms
    - I/O write: 0.000 ms

https://explain.depesz.com/s/w2Vw

Author Positive
SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" = 1
  AND "epics"."author_id" = 2
ORDER BY "epics"."id" DESC
LIMIT 20 OFFSET 0
 Limit  (cost=6.90..6.90 rows=1 width=1253) (actual time=1.997..1.997 rows=0 loops=1)
   Buffers: shared hit=3 read=2
   I/O Timings: read=1.853
   ->  Sort  (cost=6.90..6.90 rows=1 width=1253) (actual time=1.995..1.995 rows=0 loops=1)
         Sort Key: epics.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=2
         I/O Timings: read=1.853
Time: 2.359 ms
  - planning: 0.290 ms
  - execution: 2.069 ms
    - I/O read: 1.853 ms
    - I/O write: 0.000 ms

https://explain.depesz.com/s/pLuQ

Author Negative
SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" = 1
  AND "epics"."author_id" != 2
ORDER BY "epics"."id" DESC
LIMIT 20 OFFSET 0
Limit  (cost=8.37..8.38 rows=3 width=1253)
  ->  Sort  (cost=8.37..8.38 rows=3 width=1253)
        Sort Key: id DESC
        ->  Index Scan using index_epics_on_group_id on epics  (cost=0.29..8.35 rows=3 width=1253)
              Index Cond: (group_id = 1)
              Filter: (author_id <> 2)
Time: 2.162 ms
  - planning: 0.248 ms
  - execution: 1.914 ms
    - I/O read: 1.784 ms
    - I/O write: 0.000 ms

https://explain.depesz.com/s/z0kj

Edited by Eugenia Grieff

Merge request reports

Loading