Re: [GENERAL] Index greater than 8k - Mailing list pgsql-hackers

From Teodor Sigaev
Subject Re: [GENERAL] Index greater than 8k
Date
Msg-id 4548A08C.4060104@sigaev.ru
Whole thread Raw
In response to Re: [GENERAL] Index greater than 8k  ("Joshua D. Drake" <jd@commandprompt.com>)
Responses Re: [GENERAL] Index greater than 8k  ("Joshua D. Drake" <jd@commandprompt.com>)
Re: [GENERAL] Index greater than 8k  ("Gregory Maxwell" <gmaxwell@gmail.com>)
List pgsql-hackers
> We are trying to get something faster than ~ '%foo%';
> Which Tsearch2 does not give us :)

Hmm, why not with GIN? :) Although, right now GIN doesn't support prefix search,
it's possible to use it.

Brain storm method:

Develop a dictionary which returns all substring for lexeme, for example for
word foobar it will be 'foobar fooba foob foo fo oobar ooba oob oo obar oba ob
bar ba ar'. And make GIN functional index over your column (to save disk space).
So, your query will be looked as
select ... where to_tsvector(text_column) @@ 'foo';
Notices:
Time of search in GIN weak depend on number of words (opposite to
tsearch2/GiST), but insertion of row may be slow enough....

--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

pgsql-hackers by date:

Previous
From: "Simon Riggs"
Date:
Subject: Re: Extended protocol logging
Next
From: "Joshua D. Drake"
Date:
Subject: Re: [GENERAL] Index greater than 8k