Thread: BUG #15491: index on function not being used for full text searchwhen querying through a view
BUG #15491: index on function not being used for full text searchwhen querying through a view
From
PG Bug reporting form
Date:
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
Re: BUG #15491: index on function not being used for full text searchwhen querying through a view
From
Arthur Zakirov
Date:
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=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 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