Search Postgresql Archives

Re: using text search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux