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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #364722 (closed) and #411770
Edited by Javiera Tapia