Merlin Moncure <mmoncure@xxxxxxxxx> writes: > On Tue, Oct 13, 2015 at 11:54 AM, Alvaro Herrera > <alvherre@xxxxxxxxxxxxxxx> wrote: >> Be very careful with a % at the left. The index is not going to work at >> all there. It is not the same as looking for stuff without a % at the >> left. > > Note, you can optimize LIKE '%foo%' with pg_trgm/gin indexing. > Trigram based indexing is kind of a mixed bag but is about to get a > lot faster with recent enhancements so that it should mostly match or > beat the brute force search. Right, it is indeed very efficient, in particular when trying it on PG 9.5b1! Thank you. As a bonus, it is case insensitive, so even ILIKE can take advantage of it. > This is the preferred solution if you need to do partial string matching -- > for most other cases of attribute searching I'd be looking at jsonb. Speaking of which, as this is exactly the goal of my experiments, I have now added one trigram index for each "key" of an hstore field, where the "key" is the user language and the value is a text in that language: CREATE INDEX "text_it_idx" ON test_hstore USING gin ((text->'it') gin_trgm_ops) CREATE INDEX "text_en_idx" ON test_hstore USING gin ((text->'en') gin_trgm_ops) Is this the right approach, or am I missing something clever that would allow me to have a single index? > Welcome to postgres OP! Thank you. Even if I'm not exactly new to PG, I'm very glad to have at least a little opportunity to convince my coworkers to replace an awful MySQL subsystem with a shiny new implementation based on PostgreSQL! bye, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. lele@xxxxxxxxxxxxxxx | -- Fortunato Depero, 1929. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
