Re: [GENERAL] Incorrect FTS result with GIN index

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Teodor Sigaev <teodor(at)sigaev(dot)ru>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [GENERAL] Incorrect FTS result with GIN index
Date: 2010-07-29 16:13:32
Message-ID: Pine.LNX.4.64.1007292012350.32129@sn.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

On Thu, 29 Jul 2010, Tom Lane wrote:

> Oleg Bartunov <oleg(at)sai(dot)msu(dot)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.655 rows=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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-07-29 16:28:38 Re: [GENERAL] Incorrect FTS result with GIN index
Previous Message Merlin Moncure 2010-07-29 16:11:37 Re: Danger of idiomatic plpgsql loop for merging data

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-07-29 16:17:03 Re: page corruption on 8.3+ that makes it to standby
Previous Message Tom Lane 2010-07-29 16:03:07 Re: string_to_array has to be stable?