BUG #15139: Gin index limtied to configuration not used

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: cdalxndr(at)yahoo(dot)com
Subject: BUG #15139: Gin index limtied to configuration not used
Date: 2018-03-30 17:11:22
Message-ID: 152242988260.6322.11237886300088068445@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15139
Logged by: Alex
Email address: cdalxndr(at)yahoo(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Bossart, Nathan 2018-03-30 18:39:01 Re: BUG #14941: Vacuum crashes
Previous Message PG Bug reporting form 2018-03-30 14:00:29 BUG #15138: pg_ctl status doesn't find running service