#8935 closed defect (fixed)
Full text search of tickets can be very slow
| Reported by: | Owned by: | ||
|---|---|---|---|
| Priority: | normal | Milestone: | 0.11.7 |
| Component: | search system | Version: | 0.11.6 |
| Severity: | major | Keywords: | performance |
| Cc: | Branch: | ||
| Release Notes: | |||
| API Changes: | |||
| Internal Changes: | |||
Description
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.
Attachments (1)
Change History (8)
by , 15 years ago
| Attachment: | union-ticket-search.diff added |
|---|
follow-up: 2 comment:1 by , 15 years ago
Nice! Thanks for looking into this. As your patch does separate queries on each table, couldn't you also drop the a., b. and c. prefixes? I remember reading a discussion recently where somebody said this would make a big difference on PostgreSQL.
comment:2 by , 15 years ago
Replying to rblank:
Nice! Thanks for looking into this. As your patch does separate queries on each table, couldn't you also drop the
a.,b.andc.prefixes? I remember reading a discussion recently where somebody said this would make a big difference on PostgreSQL.
Dropping the table prefixes would be fine, although as far as I can tell, it doesn't have any effect on performance. The query takes the same amount of time (± a couple of milliseconds), and EXPLAIN ANALYZE shows the same query plan.
comment:3 by , 15 years ago
| Keywords: | performance added |
|---|---|
| Milestone: | → 0.11.7 |
| Severity: | normal → major |
I'll apply the patch.
follow-up: 6 comment:4 by , 15 years ago
| Resolution: | → fixed |
|---|---|
| Status: | new → closed |
Patch cleaned-up as discussed above and committed as r9007.
More tickets like this one please ;-)
follow-up: 7 comment:6 by , 15 years ago
comment:7 by , 15 years ago
Replying to rblank:
Replying to cboos:
Patch cleaned-up as discussed above and committed as r9007.
Hey, didn't we say "only critical fixes in 0.11.7"? :)
I suppose that when the search takes suddenly more than a minute compared to a few seconds with a previous version, one can see that as a critical regression…
But really for 0.11.7, it should be "critical fixes + speed enhancements when possible" (#8813).
diff against 0.11.6 for more efficient ticket search