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@xxxxxxxxxxxxxxxxxx> 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)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
