Обсуждение: Incorrect FTS query results with GIN index
Hello,
Basically, I started testing prefix matching in FTS and got into troubles. Self-contained example follows:
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 32-bit
postgres=# create table test (id int, data text, tsvd tsvector);
postgres=# insert into test (id, data) values (1, 'hot stuff is here'), (2, 'light is hotter than dark'), (3, 'nothing is that hottie');
postgres=# update test set tsvd = to_tsvector('english', data);
postgres=# select * from test;
id | data | tsvd
----+---------------------------+-------------------------------
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie | 'hotti':4 'noth':1
(3 rows)
Now let's play with queries:
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot');
id | data | tsvd
----+-------------------+-------------------
1 | hot stuff is here | 'hot':1 'stuff':2
(1 row)
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:*');
id | data | tsvd
----+---------------------------+-------------------------------
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie | 'hotti':4 'noth':1
(3 rows)
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* | hot');
id | data | tsvd
----+---------------------------+-------------------------------
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie | 'hotti':4 'noth':1
(3 rows)
Looks good so far. Let's introduce an index:
postgres=# create index ix_test on test using gin(tsvd);
CREATE INDEX
postgres=# set enable_seqscan to off;
SET
First two queries result in the same row sets, but look at the third one:
postgres=# explain select * from test where tsvd @@ to_tsquery('english', 'hot:* | hot');
QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.26..8.28 rows=1 width=68)
Recheck Cond: (tsvd @@ to_tsquery('english', 'hot:* | hot'::text))
-> Bitmap Index Scan on ix_test (cost=0.00..4.26 rows=1 width=0)
Index Cond: (tsvd @@ to_tsquery('english', 'hot:* | hot'::text))
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* | hot');
id | data | tsvd
----+-------------------+-------------------
1 | hot stuff is here | 'hot':1 'stuff':2
(1 row)
WTH? Apparently prefixed part of the query stopped working.
Interesting that the bug doesn't show up with GiST:
postgres=# drop index ix_test;
DROP INDEX
postgres=# create index ix_test on test using gist(tsvd);
CREATE INDEX
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* | hot');
id | data | tsvd
----+---------------------------+-------------------------------
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie | 'hotti':4 'noth':1
(3 rows)
Thank you for the report, will see on this weekend
Vyacheslav Kalinin wrote:
> Hello,
>
> Basically, I started testing prefix matching in FTS and got into
> troubles. Self-contained example follows:
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
> Basically, I started testing prefix matching in FTS and got into
> troubles. Self-contained example follows:
Thank you, fixed. The reason was in incorrect optimization of GIN scan: GIN
reuses scan result for equals key, but comparison of key didn't take into
account a difference of scan's strategy.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Great, thank you!
I assume this one goes into 8.4.3, right?
2010/1/18 Teodor Sigaev <teodor@sigaev.ru>
Thank you, fixed. The reason was in incorrect optimization of GIN scan: GIN reuses scan result for equals key, but comparison of key didn't take into account a difference of scan's strategy.Basically, I started testing prefix matching in FTS and got into
troubles. Self-contained example follows:
> Great, thank you! > I assume this one goes into 8.4.3, right? Yeah, or apply patch http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/gin/ginscan.c?r1=1.25&r2=1.26 -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/