Little important fact: it is not the indexing taking so long with FTS.
It is filling the fulltext table before creating indexes on it. I have
table A with with a column searchtext and trigger to create FTS records
of it in table cdsfti. So to fill the cdsfti table I did UPDATE A SET
oneintegercolumn = oneintgercolumn. oneintegercolumn does not have any
constraints or indexes what could be reason for decreased performance.
Urmo wrote:
> Hi,
>
> there seems to be no way of searching partial matches with tsearch.
> Would it be hard to implement prefix based matching, i.e.
> "hu" matches "human", "humanity", "humming", "huge"? With some hacking I
> managed to disable morphology part from tsearch1 (database contained
> multiple languages in a single table so morphology could not be used)
> and it run happily for a year. But now I needed prefix based substring
> match and I'm kinda lost. I tried using fulltextindex but it took ages
> to create the index with 100K table (query run about a day before I lost
> my pacience and canceled it). I even modified it to lose suffixes and
> index only full words but it was still too slow (50K records were
> indexed in 24h).
>
> Can anybody help or point out right direction? Or is this even (easily)
> doable with tsearch1 or tsearch2?
>
> Urmo
>