[rt-users] Full text search on postrgesql

ktm at rice.edu ktm at rice.edu
Wed Nov 30 08:47:12 EST 2011


On Wed, Nov 30, 2011 at 12:23:45PM +0000, Guadagnino Cristiano wrote:
> Hi all,
> we are experimenting the upgrade to 4.x while switching to Postgres due 
> to the easier setup of full text search (we were on mysql previously).
> 
> The update went smooth and we have RT 4.02 in a testing environment 
> where we migrated our mysql production database.
> 
> I followed the instructions for enabling full text search in postgres, 
> and left it indexing for about one day (!) our 30K ticket db.
> 
> Now I am trying to test full text searches, but I am very disappointed 
> by the performance.
> 
> First of all: our previous Sphinx proprietary implementation (see 
> "IntegrateSphinx" in the wiki) indexed all our db in about 5 minutes. I 
> think the difference is that we were not indexing attachments, while the 
> current setup indexes attachments too... am I right in this assumption?
> 
> Second: I tried a full text search on our Sphinx implementation and it 
> took 0.18 seconds to find 27 results ranging from mid-2009 to today. The 
> exact same query on RT 4.02 with postgres and FTS enabled timed out 
> after 40 seconds. Even taking attachments into account that seems too much.
> 
> Third: there is no separate search dialog for FTS. I am using the simple 
> search dialog typing "fulltext:searchedword", is it right?
> 
> Fourth: I am trying to understand if I should be using any special 
> syntax (I read something about it in postgres specific pages). To 
> understand what RT is really doing I tried enabling the statement log, 
> but I find no trace of my FTS query in it. Is it normal?
> 
> Thank you in advance.
> 
> Bye
> Cris
> 

Hi Chris,

It definitely sounds like you do not have fulltext setup correctly or
have a missing index or something. For 30k tickets, that search should
take very little time. Have the schema changes been made and any needed
configuration changes? What type of index are you using for the fulltext
search content? GIN or GIST? We are still using the recipe from the wiki
but when I checked it looked like that was the basic setup that was used
by RT 4.x, we are on 3.8.

Regards,
Ken



More information about the rt-users mailing list