[rt-users] Full text search on postrgesql

[Guadagnino Cristiano]

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

-- 
Cristiano Guadagnino
Servizio Database Administration
Bankadati S.I.
Gruppo Credito Valtellinese
Tel. +39-0342-522172