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
would wind up looking like this when using tsearch2:
( attachments.content_tsearch2_idx @@ to_tsquery(‘default’, ‘term1 &
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.
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.