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.
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.