Am Mittwoch, 29. August 2007 17:56:06 schrieb [EMAIL PROTECTED]: > Quoting [EMAIL PROTECTED]: > > Hi all, > > 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 > 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
Hi Ken, I will try tsearch2 (I hope I get it working). But I don't understand why the query is called 2 times. I thought this should be improved. 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
