Add project language search/filter (!104167) · Merge requests · GitLab.org / GitLab

Lee Tickett requested to merge 15490-add-project-language-filter into master Nov 15, 2022

What does this MR do and why?

This MR adds a language filter to the projects list.

This should open up GitLab to the world of open source contributors looking for a project of their favoured language to get stuck into 💙

Database

Query:

SELECT
        mcv
      FROM
        pg_stats
      CROSS JOIN LATERAL
        unnest(most_common_vals::text::int[]) mt(mcv)
      WHERE
        tablename = ‘repository_languages’ and attname=‘programming_language_id’

Plan

 Nested Loop  (cost=1.13..10.43 rows=10 width=4) (actual time=3.860..3.905 rows=196 loops=1)
   Buffers: shared hit=13 read=5 dirtied=1
   I/O Timings: read=3.055 write=0.000
   ->  Nested Loop  (cost=1.11..10.21 rows=1 width=401) (actual time=3.759..3.765 rows=1 loops=1)
         Buffers: shared hit=13 read=5 dirtied=1
         I/O Timings: read=3.055 write=0.000
         ->  Nested Loop  (cost=0.70..6.75 rows=1 width=10) (actual time=1.359..1.364 rows=1 loops=1)
               Buffers: shared hit=11 read=3
               I/O Timings: read=1.224 write=0.000
               ->  Index Scan using pg_class_relname_nsp_index on pg_catalog.pg_class c  (cost=0.29..3.31 rows=1 width=8) (actual time=0.053..0.057 rows=1 loops=1)
                     Index Cond: (c.relname = 'repository_languages'::name)
                     Filter: ((NOT c.relrowsecurity) OR (NOT row_security_active(c.oid)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=5
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using pg_attribute_relid_attnam_index on pg_catalog.pg_attribute a  (cost=0.41..3.44 rows=1 width=6) (actual time=1.302..1.303 rows=1 loops=1)
                     Index Cond: ((a.attrelid = c.oid) AND (a.attname = 'programming_language_id'::name))
                     Filter: ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=6 read=3
                     I/O Timings: read=1.224 write=0.000
         ->  Index Scan using pg_statistic_relid_att_inh_index on pg_catalog.pg_statistic s  (cost=0.41..3.43 rows=1 width=176) (actual time=2.392..2.393 rows=1 loops=1)
               Index Cond: ((s.starelid = a.attrelid) AND (s.staattnum = a.attnum))
               Buffers: shared hit=2 read=2 dirtied=1
               I/O Timings: read=1.831 write=0.000
   ->  Function Scan on unnest mt  (cost=0.01..0.11 rows=10 width=4) (actual time=0.099..0.114 rows=196 loops=1)
         I/O Timings: read=0.000 write=0.000

Time: 6.017 ms
  - planning: 1.943 ms
  - execution: 4.074 ms
    - I/O read: 3.055 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 13 (~104.00 KiB) from the buffer pool
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

Query:

WITH “projects_cte” AS MATERIALIZED (SELECT “projects”.* FROM “projects” INNER JOIN “project_authorizations” ON “projects”.“id” = “project_authorizations”.“project_id” WHERE “project_authorizations”.“user_id” = 1) SELECT “projects”.* FROM “projects_cte” AS “projects” INNER JOIN “repository_languages” ON “repository_languages”.“project_id” = “projects”.“id” WHERE “projects”.“pending_delete” = FALSE AND “projects”.“archived” = FALSE AND “projects”.“marked_for_deletion_at” IS NULL AND “projects”.“pending_delete” = FALSE AND “repository_languages”.“programming_language_id” = 7 AND “projects”.“hidden” = FALSE ORDER BY LOWER(“projects”.“name”) ASC LIMIT 20 OFFSET 0

Plan

 Limit  (cost=13099.07..13099.08 rows=1 width=4324) (actual time=77.516..96.748 rows=20 loops=1)
   Buffers: shared hit=72475
   I/O Timings: read=0.000 write=0.000
   CTE projects_cte
     ->  Gather  (cost=1063.86..13018.45 rows=3851 width=747) (actual time=3.628..31.036 rows=8575 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           Buffers: shared hit=39963
           I/O Timings: read=0.000 write=0.000
           ->  Nested Loop  (cost=63.86..11633.35 rows=1605 width=747) (actual time=1.017..40.496 rows=2858 loops=3)
                 Buffers: shared hit=39963
                 I/O Timings: read=0.000 write=0.000
                 ->  Parallel Bitmap Heap Scan on public.project_authorizations  (cost=63.42..6146.22 rows=1605 width=4) (actual time=0.968..8.863 rows=2858 loops=3)
                       Buffers: shared hit=5661
                       I/O Timings: read=0.000 write=0.000
                       ->  Bitmap Index Scan using project_authorizations_pkey  (cost=0.00..62.46 rows=3851 width=0) (actual time=1.688..1.689 rows=8575 loops=1)
                             Index Cond: (project_authorizations.user_id = 1)
                             Buffers: shared hit=69
                             I/O Timings: read=0.000 write=0.000
                 ->  Index Scan using projects_pkey on public.projects projects_1  (cost=0.44..3.42 rows=1 width=747) (actual time=0.010..0.010 rows=1 loops=8575)
                       Index Cond: (projects_1.id = project_authorizations.project_id)
                       Buffers: shared hit=34302
                       I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=80.62..80.62 rows=1 width=4324) (actual time=77.514..77.519 rows=20 loops=1)
         Sort Key: (lower((projects.name)::text))
         Sort Method: top-N heapsort  Memory: 48kB
         Buffers: shared hit=72475
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.56..80.61 rows=1 width=4324) (actual time=4.410..76.155 rows=461 loops=1)
               Buffers: shared hit=72472
               I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on projects_cte projects  (cost=0.00..77.02 rows=1 width=4292) (actual time=3.642..22.591 rows=7988 loops=1)
                     Filter: ((NOT projects.pending_delete) AND (NOT projects.archived) AND (projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (NOT projects.hidden))
                     Rows Removed by Filter: 587
                     Buffers: shared hit=39963
                     I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using repository_languages_pkey on public.repository_languages  (cost=0.56..3.58 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=7988)
                     Index Cond: ((repository_languages.project_id = projects.id) AND (repository_languages.programming_language_id = 7))
                     Heap Fetches: 178
                     Buffers: shared hit=32509
                     I/O Timings: read=0.000 write=0.000

Time: 99.859 ms
  - planning: 1.309 ms
  - execution: 98.550 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 72475 (~566.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Screenshots or screen recordings

Screen_Recording_2022-11-15_at_22.22.45

How to set up and validate locally

Enabled project_language_search from the rails console: Feature.enable(:project_language_search)

http://gdk.test:3000/?name=under&sort=name_asc&name=lab&sort=name_asc&language=shell

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.

Approvals

Category	Reviewer	Maintainer
backend	✅ `@Quintasan`	✅ `@minac`
database	✅ `@ahegyi`	✅ `@mayra-cabrera`
frontend	✅ `@leipert`	✅ `@justin_ho`
test for `spec/features/*`	✅ `@rzwambag`	n/a
UX	✅ `@mnichols1`	n/a

Edited Dec 19, 2022 by Lee Tickett

Add project language search/filter