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)
