BUG #15491: index on function not being used for full text search when querying through a view
The following bug has been logged on the website:
Bug reference: 15491
Logged by: Allen Wu
Email address: allenwu1973@yahoo.co.jp
PostgreSQL version: 11.0
Operating system: linux
Description:
Hi
I'm trying to implement full text search with PostgreSQL,
I found when using a function create index in conjunction with querying
through a view, will cause postgres not to use the index.
Below are example for each scenario.
When only using expression index, the index is used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=16ed7a04389ebf71dc5a1fb2daa34058
When only using view, the index is also used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e8d50181b8612421773c2da39e45c9fd
view + expression, the index is NOT used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=19d7b923514f100f6e402b54624cc972
I've avoid using function in my app, but I think this should have worked.
Thank you
Hello,
On 07.11.2018 11:30, PG Bug reporting form wrote:
I'm trying to implement full text search with PostgreSQL,
I found when using a function create index in conjunction with querying
through a view, will cause postgres not to use the index.
Below are example for each scenario.When only using expression index, the index is used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=16ed7a04389ebf71dc5a1fb2daa34058When only using view, the index is also used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e8d50181b8612421773c2da39e45c9fdview + expression, the index is NOT used
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=19d7b923514f100f6e402b54624cc972I've avoid using function in my app, but I think this should have worked.
Thank you
I'm not strong in various function volatility categories. But it seems
that STABLE function solves this. See:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=eadfe2af10113ca53ff9698228728490
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company