Re: using text search

Ha! Got it!

for enybody whois interested:

with tst(regexp) as (SELECT '(' || array_to_string(array_agg(phrase),
'|') || ')' from KEYWORDS) select o.* from ORDERS o, tst t where o.info
~ t.regexp;

execution time: 6400ms. (keywords=4, orders=1mln)

BTW: does anybody know if there is an index, that could improve the
performence of the above regexp?

-R

W dniu 04/23/2013 02:30 PM, Alfonso Afonso pisze:
> Hi Rafal
>
> This function returns the position where the substring is found, so you could do a query with clause position(table1.field in table2.field)
>
> The 0 result is not found and maybe, in your case, is faster the use of internal text functions instead of like comparison... hope helps.
>
> Bye
>
> El 23/04/2013, a las 11:24, Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> escribió:
>
>> W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze:
>>> I forgot to say that the function is "position ( txtseach in txtcomplete)" :)
>>>
>>> Bye
>>
>> Alfonso, thenx
>>
>> But if I may: How can I use that function? In a context of my problem?
>>
>> then again. At the edge of desperation, I'm thinking of writing a function, that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause by string operations, and then EXECUTE it. With (currently) four keywords, I'd expect such function to return results within 5 seconds at most.
>>
>> but I'd expect that there should be a way to "tell this" to postgresql SQL directly. Isn't it?
>>
>>
>> -R
>>
>>
> Alfonso Afonso
> (personal)
>
>
>
>
>
>
>