Hi Ken, On Tue, Jun 08, 2010 at 07:48:15AM -0500, Kenneth Marshall wrote: > ILIKE is fine on PostgreSQL as long as the search is anchored on > the left and there is an index. Otherwise it will do a sequential > scan of the attachments table to find matching records. The wiki > entries for full-text indexing with PostgreSQL and Oracle can avoid > the sequential scan through the use of the database's full-text
thanks for your work on PostgreSQLFullText wiki page! Good starting point. I'm playing with the fulltext on one instance already. I must discuss the possibility to use the fulltext on Pg with my leader. The results are different from those get from ILIKE. I tried to find 'cisco' for example :). Normal search returned 25 tickets, but fulltext only 15. Fulltext parser did tokens from URL and www.cisco.com was a lexem as is (not broken further). I can't say this is fine or not, it is difference. Users must know this - how to write the query correctly. > index support. I do agree that patching the actual DBIx::SearchBuilder > is not preferable. Unfortunately, since that is the API that is used > to provide a database abstraction layer and it does not yet include > the ability to specify full-text indexing details, you have to patch > it. If it is any consolation, the patch is much, much simpler (trivial) > for the PostgreSQL support versus the version that is needed for > OracleText. :) I completely understand. I put the package libdbix-searchbuilder-perl (Debian) on hold in aptitude, so sysadmin should remember, that this package needs a special handling. I did a copy of SearchBuilder.pm file into <rt-prefix>/local/lib/DBIx/ and did a modification. This perl path is searched first, so a modification is beside other RT code... I have prepared a script for converting data from Mysql to Pg and for adding/removing fulltext support based on your wiki page. Maybe it can be added to wiki for someone else to play with fulltext and Pg. I must to test it a bit and switch some fixed parts into conditional blocks controlled by command-line options. Regards -- Zito Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com