Edgewall Software
Modify

Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#8935 closed defect (fixed)

Full text search of tickets can be very slow

Reported by: khym@… Owned by: khym@…
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)

union-ticket-search.diff (1.9 KB ) - added by khym@… 14 years ago.
diff against 0.11.6 for more efficient ticket search

Download all attachments as: .zip

Change History (8)

by khym@…, 14 years ago

Attachment: union-ticket-search.diff added

diff against 0.11.6 for more efficient ticket search

comment:1 by Remy Blank, 14 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.

in reply to:  1 comment:2 by khym@…, 14 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. and c. 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 Christian Boos, 14 years ago

Keywords: performance added
Milestone: 0.11.7
Severity: normalmajor

I'll apply the patch.

comment:4 by Christian Boos, 14 years ago

Resolution: fixed
Status: newclosed

Patch cleaned-up as discussed above and committed as r9007.

More tickets like this one please ;-)

Last edited 14 years ago by Christian Boos (previous) (diff)

comment:5 by Christian Boos, 14 years ago

Owner: set to khym@…

Fix ported to trunk in r9008.

in reply to:  4 ; comment:6 by Remy Blank, 14 years ago

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"? :)

More tickets like this one please ;-)

Ditto.

in reply to:  6 comment:7 by Christian Boos, 14 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).

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain khym@….
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from khym@… to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.