[rt-users] slow content queries

[Kevin Murphy]

Another downside of my users' penchant for attaching large files is that 
content queries using the query builder are unusably slow now.  I've 
noticed that content queries blindly search all attachments, including 
non-text attachments.  Changing content searches to only search 
text-type attachments results in significant performance increases (33 
seconds down to 16 seconds in a test query -- I ran each query multiple 
times to get stable numbers).  However, most of the time when I do a 
content search, I really only expect to be searching the text of in-line 
comments and replies, and it would really help if content queries could 
have a content-type modifier, with normal in-line message content being 
the default.  If I restrict a content search to only search attachments 
with no filenames ("and attachments.filename is null"), the speed of my 
test query improves to 1.4 seconds.  Where would be the right spot to 
inject this additional condition into the RT code?

I've also been wondering whether it would someday be possible for for 
DBIx::SearchBuilder to be modified to optionally support PostgreSQL's de 
facto official full-text search engine, tsearch2?

A query that generated SQL like this:

( (attachments.content ilike '%term1%')  and (attachments.content ilike 
'%term2%') )

would wind up looking like this when using tsearch2:

( attachments.content_tsearch2_idx @@ to_tsquery('default', 'term1 & 
term2') )

where content_tsearch2_idx is a new column (tsearch2 requires a 
dedicated column for its indexing purposes).

I suppose the translation could also look like this:

( (attachments.content_tsearch2_idx @@ to_tsquery('default', 'term1')) 
and (attachments.content_tsearch2_idx @@ to_tsquery('default', 'term2')))

but it might not be as efficient.

Thanks,
Kevin Murphy

P.S.  For posterity, I'm using RT 3.4.4, PostgreSQL 8.0.4, 
DBIx::SearchBuilder v1.35, Perl 5.8.7, on Mac OS X 10.4.3.