Comment 8 for bug 607960

So this is currently performing better and more consistently on edge and production. The bloated indexes where causing some problems, and this has been fixed. I need to write and schedule reports to pick up these issues earlier and preemptively fix them.

The big problem is the ILIKE '%foo%' substring search on BugTask.targetnamecache. On production, it consistently takes a little over a second. At the moment, we don't have infrastructure to do substring searches against this much data quickly enough so if we need a quick fix we need to drop this clause, relying on the full text query.

If we can't drop this clause, we will need a new index type like Wildspeed (http://www.sai.msu.su/~megera/wiki/wildspeed -- written by the same people who wrote tsearch2), trigrams (in postgresql-contrib), or NIH it and roll our own using a pair of tables and standard indexes for prefix searches and more complex queries.