Re: BUG #14518: FTS index not triggered when using function to provide the tsquery

Thank you Tom!
That makes perfect sense. It is weird that it works using the CTE though.

Thanks again,
Francisco

On Fri, Jan 27, 2017 at 2:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> grau(dot)fran(at)gmail(dot)com writes:
> > But I need to run the query through a function to generate a custom
> tsquery
> > sometimes. So if I run:
> > SELECT pub.publication_id
> > FROM publication pub
> > WHERE
> > pub.fts @@ x_get_tsquery('Omnis')
>
> > "Seq Scan on publication pub (cost=0.00..5939.89 rows=50 width=8)"
> > " Filter: (fts @@ x_get_tsquery('Omnis'::text))"
>
> > As you can see it uses a sequential scan... not good.
>
> Most likely you've marked that function volatile (or allowed it to be so
> marked by default). That disables using it in an index condition,
> because the planner has to assume that the function's result might change
> for every row. You need to mark it stable or immutable instead, per
> https://www.postgresql.org/docs/devel/static/xfunc-volatility.html
>
> regards, tom lane
>

--
Francisco Grau