[rt-users] rt 3.0.10 / pg 7.4.2 keyword search hanging

[Kevin Murphy]

All searches for keywords in "attachments" are causing the postgres 
query process to hang, consuming all CPU.

I'm using:
Perl 5.8.3
Apache 1.3.29
Mod_perl 1.29
PostgreSQL 7.4.2
DBIx::SearchBuilder .99
RT 3.0.10

I upgraded about a week ago from 3.0.9 to 3.0.10, and at the same time 
I upgraded from PostgreSQL 7.3.4 to 7.4.2.  (I dumped the database and 
re-slurped it per PG instructions, hopefully).  Unfortunately, I'm not 
sure if I tested whether keyword search (for "attachment") was working 
after the upgrade, but it doesn't seem to be working now.

A sample query from the postgres log is:

SELECT count(DISTINCT main.id)
	FROM Tickets main , Transactions Transactions_1, Attachments 
Attachments_2
	WHERE             ((Attachments_2.TransactionId ILIKE 
Transactions_1.id))
			AND ((main.EffectiveId = main.id))
			AND ((main.Status != 'deleted'))
			AND ((main.Type = 'ticket'))
			AND ((main.id ILIKE Transactions_1.Ticket))
			AND ( ( ( (Attachments_2.Content ILIKE '%summary page%') ) ) )

I don't know anything about databases, but I ran 'explain' in case it 
helps any gurus out there:

  Aggregate  (cost=576.80..576.80 rows=1 width=4)
    ->  Nested Loop  (cost=0.00..576.80 rows=1 width=4)
          Join Filter: (("outer".id)::text ~~* ("inner".ticket)::text)
          ->  Seq Scan on tickets main  (cost=0.00..43.13 rows=1 width=4)
                Filter: ((effectiveid = id) AND ((status)::text <> 
'deleted'::text) AND (("type")::text = 'ticket'::text))
          ->  Nested Loop  (cost=0.00..533.14 rows=30 width=4)
                Join Filter: (("outer".transactionid)::text ~~* 
("inner".id)::text)
                ->  Seq Scan on attachments attachments_2  
(cost=0.00..295.77 rows=1 width=4)
                      Filter: (content ~~* '%summary page%'::text)
                ->  Seq Scan on transactions transactions_1  
(cost=0.00..132.77 rows=5977 width=8)
(1623 rows)

Any suggestions for how I should proceed to fix this?

Thanks,
Kevin Murphy