Re: [GENERAL] Incorrect FTS result with GIN index

On Thu, 29 Jul 2010, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
>> I also wonder why did I get "right" result :) Just repeated the query:
>
>> test=# select count(*) from search_tab where (to_tsvector('german', keywords ) @@ to_tsquery('german', 'ee:* &
dd:*'));
>>   count
>> -------
>>     123
>> (1 row)
>
> Yeah, that case works (though I think it's unnecessarily slow).  The one
> that gives the wrong answer is the equivalent form with two AND'ed @@
> operators.

hmm, that query works too :)

test=# select count(*) from search_tab where (to_tsvector('german', keywords ) @@ to_tsquery('german', 'ee:*')) and
(to_tsvector('german',keywords ) @@ to_tsquery('german', 'dd:*')); count 
 
-------   123
(1 row)

Time: 26.155 ms


test=# explain analyze select count(*) from search_tab where (to_tsvector('german', keywords ) @@ to_tsquery('german',
'ee:*'))and (to_tsvector('german', keywords ) @@ to_tsquery('german', 'dd:*'));
                                     QUERY PLAN 
 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=103.87..103.88 rows=1 width=0) (actual time=22.819..22.820 rows=1 loops=1)   ->  Bitmap Heap Scan on
search_tab (cost=5.21..103.80 rows=25 width=0) (actual time=22.677..22.799 rows=123 loops=1)         Recheck Cond:
((to_tsvector('german'::regconfig,keywords) @@ '''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))        ->  Bitmap Index Scan on idx_keywords_ger  (cost=0.00..5.21 rows=25 width=0) (actual
time=22.655..22.655rows=123 loops=1)               Index Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery)AND (to_tsvector('german'::regconfig, keywords) @@ '''dd'':*'::tsquery)) Total runtime: 22.865 ms
 


    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83