Ускорить выполнение запроса 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))"