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