Re: [BUGS] BUG #14518: FTS index not triggered when using function to provide the tsquery - Mailing list pgsql-bugs

grau.fran@gmail.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs