BUG #14518: FTS index not triggered when using function to provide the 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
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@sss.pgh.pa.us> wrote:
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.htmlregards, tom lane
--
Francisco Grau
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