From: | Lele Gaifax <lele(at)metapensiero(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Understanding "seq scans" |
Date: | 2015-10-15 06:48:35 |
Message-ID: | 87bnc0vd8c.fsf@metapensiero.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Oct 13, 2015 at 11:54 AM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> 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(at)metapensiero(dot)it | -- Fortunato Depero, 1929.
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier Dony | 2015-10-15 07:48:38 | Re: Serialization errors despite KEY SHARE/NO KEY UPDATE |
Previous Message | Michael Paquier | 2015-10-15 05:39:06 | Re: How to get the session user in a C user defined function |