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

[ktm at rice.edu]

Quoting 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
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> 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