Incorrect FTS query results with GIN index

Started by Vyacheslav Kalininabout 16 years ago5 messagesgeneral
Jump to latest

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)

#2Teodor Sigaev
teodor@sigaev.ru
In reply to: Vyacheslav Kalinin (#1)
Re: Incorrect FTS query results with GIN index

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/

#3Teodor Sigaev
teodor@sigaev.ru
In reply to: Vyacheslav Kalinin (#1)
Re: Incorrect FTS query results with GIN index

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/

In reply to: Teodor Sigaev (#3)
Re: Incorrect FTS query results with GIN index

Great, thank you!
I assume this one goes into 8.4.3, right?

2010/1/18 Teodor Sigaev <teodor@sigaev.ru>

Show quoted text

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/

#5Teodor Sigaev
teodor@sigaev.ru
In reply to: Vyacheslav Kalinin (#4)
Re: Incorrect FTS query results with GIN index

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&amp;r2=1.26

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/