Skip to content

Geo - Support filtering pages deployment by keyword via GraphQL

What does this MR do and why?

Currently, for self-service replicables, we are unable to do any filtering on the Geo Replicable views.

This MR supports filtering pages deployment registries by keyword (fuzzy_search) via GraphQL:

Registry Searchable attributes
Pages Deployments File

Database

Raw SQL and query plans

Using:

PagesDeployment.search('filename123')

Then:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15094/commands/52483

SELECT
    pages_deployments.*
FROM
    pages_deployments
WHERE
    pages_deployments.file ILIKE '%filename123%';

Query plan (without index):

 Gather  (cost=1000.00..35680.26 rows=1 width=133) (actual time=1900.736..1906.042 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared read=8175 dirtied=1285
   I/O Timings: read=5175.634 write=0.000
   ->  Parallel Seq Scan on public.pages_deployments  (cost=0.00..34680.16 rows=1 width=133) (actual time=1893.009..1893.010 rows=0 loops=3)
         Filter: (pages_deployments.file ~~* '%filename123%'::text)
         Rows Removed by Filter: 126778
         Buffers: shared read=8175 dirtied=1285
         I/O Timings: read=5175.634 write=0.000

Statistics (without index):

Time: 1.908 s  
  - planning: 1.758 ms  
  - execution: 1.906 s  
    - I/O read: 5.176 s  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 0 from the buffer pool  
  - reads: 8175 (~63.90 MiB) from the OS file cache, including disk I/O  
  - dirtied: 1285 (~10.00 MiB)  
  - writes: 0

Then:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15135/commands/52590

Query plan (with index):

 Bitmap Heap Scan on public.pages_deployments  (cost=30.00..31.51 rows=1 width=133) (actual time=0.148..0.149 rows=0 loops=1)
   Buffers: shared hit=19
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Index Scan using index_pages_deployments_on_file_trigram  (cost=0.00..30.00 rows=1 width=0) (actual time=0.145..0.146 rows=0 loops=1)
         Index Cond: (pages_deployments.file ~~* '%filename123%'::text)
         Buffers: shared hit=19
         I/O Timings: read=0.000 write=0.000

Statistics (with index):

Time: 3.074 ms  
  - planning: 2.752 ms  
  - execution: 0.322 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 19 (~152.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 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.

Related to #364722 (closed) and #411770

Edited by Javiera Tapia

Merge request reports