Hi all, we have about 18000 Tickets in our RT and the fulltext search is nearly unusable. One search takes about 210 Seconds. We use rt3.6.4 with postgresql.
At the postgres logfile I saw that this query is called twice for the ticket count and (nearly the same) for the output (it takes every time 60 Seconds): 2007-08-29 02:24:52 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND ( ( ( Attachments_2.Content ILIKE '%test%' ) ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) 2007-08-29 02:26:09 CEST LOG: Dauer: 76995.042 ms 2007-08-29 02:26:09 CEST LOG: Anweisung: SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND ( ( ( Attachments_2.Content ILIKE '%test%' ) ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) 2007-08-29 02:27:15 CEST LOG: Dauer: 65809.656 ms 2007-08-29 02:27:15 CEST LOG: Anweisung: SELECT DISTINCT main.* FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND ( ( ( Attachments_2.Content ILIKE '%test%' ) ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 50 2007-08-29 02:28:18 CEST LOG: Dauer: 62832.487 ms Is it possible to merge these 3 queries into one? This should speed up the fulltext search about 60% Exists there any patch for this behaviour? regards Sven _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
