The following bug has been logged on the website:
Bug reference: 15139
Logged by: Alex
Email address: cdalxndr@yahoo.com
PostgreSQL version: 10.3
Operating system: Windows 10
Description:
Having the following index:
CREATE INDEX product_en_idx ON product USING GIN (lexeme) WHERE language =
'en' :: REGCONFIG;
(The column product.lexeme is of type 'tsvector')
When issuing a query from my java hibernate app, it is not using this index
(from pg log):
2018-03-30 19:43:51.902 EEST [4780] LOG: duration: 3665.170 ms execute
<unnamed>: /* dynamic native SQL query */ select count(*)
from product product
where product.lexeme @@ plainto_tsquery(cast($1 as regconfig), $2) and
product.language = cast($3 as regconfig)
2018-03-30 19:43:51.902 EEST [4780] DETAIL: parameters: $1 = 'en', $2 =
'some query', $3 = 'en'
Removing the where from index, fixes this problem and the query runs fast:
CREATE INDEX product_lexeme_idx ON dev.product USING gin(lexeme)
The query planner should also use the first index, as it contains 'where
product.language = en'.
Note that manually running this query with inline arguments in pgadmin4
query tool, the first index is used correctly. This issue replicates only
with queries from my app.