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

sk at net-lab.net sk at net-lab.net
Wed Aug 29 17:22:05 EDT 2007


Am Mittwoch, 29. August 2007 17:56:06 schrieb ktm at rice.edu:
> Quoting sk at net-lab.net:
> > 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



More information about the rt-users mailing list