Обсуждение: [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

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

Francisco Grau <grau.fran@gmail.com> writes:
> That makes perfect sense. It is weird that it works using the CTE though.

Well, the CTE forces single evaluation, so that the value is a constant
again from the outer query's perspective.

            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