Full text search on postrgesql

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

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