Re: [HACKERS] Searching for substring with tsearch(1/2)

[Urmo]

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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend