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)"
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 |