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

From: grau(dot)fran(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14518: FTS index not triggered when using function to provide the tsquery
Date: 2017-01-27 10:39:39
Message-ID: 20170127103939.1431.54363@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14518
Logged by: Francisco Grau
Email address: grau(dot)fran(at)gmail(dot)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)"

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-01-27 13:53:51 Re: BUG #14518: FTS index not triggered when using function to provide the tsquery
Previous Message SCHAUHUBER Hermann 2017-01-27 05:16:57 data inconsistency between client and server at pg_ctl stop -m fast