[rt-users] Fulltext search executes expensive query twice/ three times

[sk at net-lab.net]

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