Ускорить выполнение запроса PostgreSQL

Есть запрос, который выполняет поиск. Если в качестве триграммы поиска выступает строка без цифр, поиск происходит довольно быстро, при появлении цифр запрос выполняется довольно долго. Может кто-то подскажет в какую сторону копать для ускорения. Пример медленного запроса

SELECT products.id, products.name, products."externalId", article, "stockBalance", "categoryId", 
brand, prices."price", 
similarity("name", '123')  AS smlName ,similarity("brand", '123') AS smlBrand, 
similarity("externalId", '123')  AS smlExternalId, 
similarity("OEM", '123')  AS smlOEM, similarity("article", '123')  AS smlArticle 
FROM   public."Products" as products
LEFT JOIN public."Prices" as prices ON products.id = prices."productId" AND prices."accountId" = 6
WHERE prices."price" is not NULL  AND prices."price" > 0 and products."active" = true and 
products."stockBalance" > 0  
and ("brand" %  '123' or "name" %  '123' or "externalId" %  '123' or "OEM" %  '123' or "article" %  
'123') 
ORDER BY
case
when (similarity("name", '123')  * 5) < similarity("brand", '123') 
and  (similarity("brand", '123') * 5) > similarity("article", '123')  
and (similarity("brand", '123') * 5) > similarity("OEM", '123') 
and (similarity("brand", '123') * 5) > similarity("externalId", '123')  
then    similarity("brand", '123')

    when (similarity("name", '123') * 5) < similarity("article", '123')   
and  (similarity("article", '123') * 5) > similarity("OEM", '123') 
and  (similarity("article", '123') * 5) > similarity("externalId", '123')  
then    similarity("article", '123')

    when (similarity("name", '123') * 5) < similarity("OEM", '123')   
and (similarity("OEM", '123') * 5) > similarity("externalId", '123') 
then    similarity("OEM", '123')

    when (similarity("name", '123') * 5) < similarity("externalId", '123') 
then    similarity("externalId", '123')

else    similarity("name", '123')

end DESC LIMIT 30

По инструкции я создал индекс

CREATE INDEX trgm_externalId_idx ON public."Products" USING GIN ("externalId" gin_trgm_ops, "brand" 
gin_trgm_ops, "name" gin_trgm_ops, "article" gin_trgm_ops,"OEM" gin_trgm_ops)

Я понимаю, что запрос достаточно костыльный, но с его помощью я получаю довольно качественные результаты поиска, если я изобретаю велосипед ткните носом в какую сторону почитать. Заранее спасибо

План запроса выглядит так:

"Limit  (cost=2914.89..2914.96 rows=30 width=159)"
"->  Sort  (cost=2914.89..2915.20 rows=124 width=159)"

"Sort Key: (CASE 
WHEN (((similarity(products.name, '123'::text) * '5'::double precision) < similarity(products.brand, '123'::text)) 
AND ((similarity(products.brand, '123'::text) * '5'::double precision) > similarity(products.article, '123'::text)) AND ((similarity(products.brand, '123'::text) * '5'::double precision) > similarity(products."OEM", '123'::text)) AND ((similarity(products.brand, '123'::text) * '5'::double precision) > similarity(products."externalId", '123'::text))) THEN similarity(products.brand, '123'::text) WHEN (((similarity(products.name, '123'::text) * '5'::double precision) < similarity(products.article, '123'::text)) AND ((similarity(products.article, '123'::text) * '5'::double precision) > similarity(products."OEM", '123'::text)) AND ((similarity(products.article, '123'::text) * '5'::double precision) > similarity(products."externalId", '123'::text))) THEN similarity(products.article, '123'::text) WHEN (((similarity(products.name, '123'::text) * '5'::double precision) < similarity(products."OEM", '123'::text)) AND ((similarity(products."OEM", '123'::text) * '5'::double precision) > similarity(products."externalId", '123'::text))) THEN similarity(products."OEM", '123'::text) WHEN (((similarity(products.name, '123'::text) * '5'::double precision) < similarity(products."externalId", '123'::text)) OR (similarity(products."externalId", '123'::text) = '1'::double precision)) THEN similarity(products."externalId", '123'::text) ELSE similarity(products.name, '123'::text) END) DESC"

"->  Nested Loop  (cost=363.30..2911.22 rows=124 width=159)"

" ->  Bitmap Heap Scan on "Products" products  (cost=362.88..1359.91 rows=188 width=139)"

"Recheck Cond: ((brand % '123'::text) OR (name % '123'::text) OR ("externalId" % '123'::text) OR ("OEM" % '123'::text) OR (article % '123'::text))"

"Filter: (active AND ("stockBalance" > 0))"

"->  BitmapOr  (cost=362.88..362.88 rows=352 width=0)"

"->  Bitmap Index Scan on trgm_externalid_idx  (cost=0.00..72.53 rows=70 width=0)"

"Index Cond: (brand % '123'::text)"

"->  Bitmap Index Scan on trgm_externalid_idx  (cost=0.00..72.53 rows=70 width=0)"

"Index Cond: (name % '123'::text)"

"->  Bitmap Index Scan on trgm_externalid_idx  (cost=0.00..72.53 rows=70 width=0)"

"Index Cond: ("externalId" % '123'::text)"

"->  Bitmap Index Scan on trgm_externalid_idx  (cost=0.00..72.53 rows=70 width=0)"
" Index Cond: ("OEM" % '123'::text)"

"->  Bitmap Index Scan on trgm_externalid_idx  (cost=0.00..72.53 rows=70 width=0)"

"Index Cond: (article % '123'::text)"

" ->  Index Scan using prices_account_id_product_id on "Prices" prices  (cost=0.42..8.17 rows=1 width=12)"

"Index Cond: (("accountId" = 6) AND ("productId" = products.id))"

"Filter: ((price IS NOT NULL) AND (price > '0'::double precision))"

Ответы (0 шт):