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