XRAY: How to solve the slow query exists in Xray

Introduction 
After manually scanning all existing artifacts, you may find that the execution speed is very slow, and significant CPU consumption is observed in both Xray and the database.

Root Cause
In such scenario, you may find slow query(occurred 17s) of the below sql  from debug log and it would make server’s CPU higher and higher;

SELECT
pv.id,
pv.vuln_id,
pv.package_type,
pv.type,
pv.summary,
pv.provider,
pv.description,
pv.severity,
pv.created,
pv.ignored,
pv.is_premium,
pv.modified,
pv.edited,
pv.leading_severity,
pv.leading_severity_source,
components.id as component_id,
components.name as component_name,
vulnerable_versions.id as vulnerable_versions_id,
vulnerable_versions.version_range as vulnerable_versions_range,
vulnerable_versions.group_id as vulnerable_versions_group_id
FROM public_vulnerabilities pv
LEFT JOIN public_vulnerabilities_components components ON pv.id = components.public_vulns_tbl_id
LEFT JOIN public_vulns_comps_vulnerable_versions vulnerable_versions ON components.id = vulnerable_versions.public_vulns_comps_tbl_id WHERE components.name like '%' || 'jammy:rl-event-consumer' AND pv.package_type = 'debian' AND pv.ignored = 0 AND pv.is_premium = 0;

Resolution 
Due to the fuzzy matching of the "name" field, it can lead to index failure, resulting in a full table scan, which increases time consumption. Versions of xray after 3.70.x support the pg_trgm module, which can create GIN indexes to optimize query speed. For older versions of xray before 3.70.x, installing pg_trgm manually is required after the version upgrade.

Command to install pg_trgm module at pg database;

\c xraydb
CREATE EXTENSION pg_trgm;
Command to check index:
SELECT tablename, indexname FROM pg_indexes WHERE indexdef LIKE '%gin%trgm%';

Command to create index:

CREATE INDEX public_vulnerabilities_components_name_trgm_idx ON public.public_vulnerabilities_components USING gin (name gin_trgm_ops)
CREATE INDEX custom_vulnerabilities_components_name_trgm_idx ON public.custom_vulnerabilities_components USING gin (name gin_trgm_ops)

Afterwards, it decreases the time of sql and cpu rate, which enhances performance obviously.