[BUGS] BUG #14518: FTS index not triggered when using function to providethe tsquery

The following bug has been logged on the website:

Bug reference:      14518
Logged by:          Francisco Grau
Email address:      grau.fran@gmail.com
PostgreSQL version: 9.5.5
Operating system:   Fedora Linux 25
Description:

I have a table with a tsvector column called 'fts'. Then a GIN index on that
table.

If I run:
SELECT pub.publication_id
FROM publication pub
WHERE
  pub.fts @@ to_tsquery('Omnis:*')

That uses the index:
"Bitmap Heap Scan on publication pub  (cost=180.18..4881.63 rows=4636
width=8)"
"  Recheck Cond: (fts @@ to_tsquery('Omnis:*'::text))"
"  ->  Bitmap Index Scan on idx_publication_fts  (cost=0.00..179.02
rows=4636 width=0)"
"        Index Cond: (fts @@ to_tsquery('Omnis:*'::text))"


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.

If I run:
SELECT to_tsquery('Omnis:*') = x_get_tsquery('Omnis')
That returns TRUE

On the other hand, if I use the function inside a CTE it works as
expected:
WITH query AS (SELECT x_get_tsquery('Omnis') AS tsquery)
SELECT pub.publication_id
FROM publication pub, query
WHERE
  pub.fts @@ query.tsquery

"Nested Loop  (cost=8.65..191.36 rows=50 width=8)"
"  CTE query"
"    ->  Result  (cost=0.00..0.26 rows=1 width=0)"
"  ->  CTE Scan on query  (cost=0.00..0.02 rows=1 width=32)"
"  ->  Bitmap Heap Scan on publication pub  (cost=8.39..190.58 rows=50
width=698)"
"        Recheck Cond: (fts @@ query.tsquery)"
"        ->  Bitmap Index Scan on idx_publication_fts  (cost=0.00..8.38
rows=50 width=0)"
"              Index Cond: (fts @@ query.tsquery)"



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