RubyForge is running a rather old version of GForge - 4.0.2. It's pretty far out of date, and hopefully I'll be able to upgrade it soonish. But anyhow, Daniel Berger had reported that searching tracker items was extremely slow. Just searching a single project's support tracker took a few minutes... which pegged RubyForge's CPU, blah.

Here's the old query:

SELECT DISTINCT ON (a.group_artifact_id,a.artifact_id) a.group_artifact_id,
 a.artifact_id,a.summary,a.open_date,users.realname
FROM artifact a
LEFT OUTER JOIN artifact_message am USING (artifact_id), users
WHERE a.group_artifact_id='102'
  AND users.user_id=a.submitted_by
    AND ((a.details ILIKE '%wiki%')
      OR (a.summary ILIKE '%wiki%'))
      OR (am.body ILIKE '%wiki%')
ORDER BY group_artifact_id ASC, a.artifact_id ASC

And here's the new, much faster query:

SELECT DISTINCT ON (a.group_artifact_id,a.artifact_id) a.group_artifact_id,
 a.artifact_id,a.summary,a.open_date,users.realname
FROM artifact a
LEFT OUTER JOIN artifact_message am USING (artifact_id), users
WHERE a.group_artifact_id='102'
  AND users.user_id=a.submitted_by
    AND ((a.details ILIKE '%wiki%')
      OR (a.summary ILIKE '%wiki%')
      OR (am.body ILIKE '%wiki%'))
ORDER BY group_artifact_id ASC, a.artifact_id ASC

See the difference? It's quite small - just moved a parenthesis so that the second OR condition wasn't floating out there on its own. This prevents a full table scan of artifact_messages - which only has 4600 records or so but scanning the text of all those message bodies isn't cheap.

At any rate, you can see the new query in action here. Snappy!

In retrospect, I should have checked the latest code from GForge - because this is fixed in GForge 4.5.6. So Tim Perdue and his team are on the ball, good times. Ah well, it was an interesting experience, spelunking around in SQL; indi runs on Rails and so ActiveRecord shields me from most of that.