Full text search of tickets can be very slow
The fix for #2530 has made the full text search of tickets very slow on my installation (Trac 0.11.6, PostgreSQL 8.4.2). A search that used to take a few seconds was now taking over a minute, with Postgres using all the CPU during that time. After some discussion on this trac-users thread, I found that the performance problem is due to the database server needing to search through hundreds of thousands of rows—since the SELECT is JOINing ticket, ticket_change, and ticket_custom, the total number of rows that need to be searched grows very quickly. In the case of my installation with about 6500 tickets, the DB server had to search through about 345000 rows.
Instead of JOINing the three tables, it'd be more efficient to search each table individually and UNION the results. With that change, the search that took over a minute takes only a few seconds. Attached is a patch that implements the more efficient query. It works with a PostgreSQL DB, but I haven't tested it with other DBs.
normal → major
new → closed