Quoting [EMAIL PROTECTED]: > 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 > > Sven,
A fulltext search performs a sequential scan of your entire database. You may wish to modify the Oracle Text modifications to support full text indexing with PostgreSQL. You will need to install the tsearch2 contrib module. I will be doing this in our upcoming move to the 3.6 version of RT and will update the wiki to cover the changes needed. If you need it now, then you will need to roll your own. Look on the wiki for the Oracle full text support posted by Joop. Good luck. Ken _______________________________________________ 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
