On Jan 4, 2010, at 2:42 PM, David Huang wrote: > Hi, I had been running Trac 0.11 with a PostgreSQL database for a > while, and recently upgraded it to 0.11.6. After the upgrade, the > full text search is much slower than it was in 0.11, and while it's > searching, Postgres is using all the CPU. I believe this is due to > the changes for ticket #2530, which changed the search to include > ticket custom fields. I noticed the ticket_custom table didn't have > any indexes, so I added one on the ticket column, but that didn't > seem to help any. I'm no Postgres guru, so adding the index is all I > could think of. Does anyone have any ideas on how to speed up the > search? A search for a single word used to take a few seconds with > 0.11; it now takes over a minute.
Hi David, I could be wrong, but this is more of a generic SQL problem than a Postgres problem. You need to find out what columns are being accessed during a full text search in order to decide where an index might help. You might find that easiest to do with Trac. Alternatively (and here's where it gets Postgres-specific), you could turn on Postgres logging via postgresql.conf with the log_statement param set to "all". You'll need to set some other params, too, like log_destination, etc. My Postgres skills are a bit rusty and I don't have this stuff in my head anymore, so check the manual for details. Setting log_statement=all will cause every query executed to be written to the log. This will kill your performance, so you don't want to leave this on any longer than you have to. If you log all queries, you'll get to see the exact SQL that's executed when you do a full-text search. Copy and paste that SQL into a text file, and then use psql (Postgres' interactive command line) to play around with it. You can add an index to a column and re-run the query to see if the run time improves. (Beware of caching which can skew your results.) Hope this helps Philip -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to trac-us...@googlegroups.com. To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
