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 <ra...@zorro.isa-geek.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)










--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to