Search Postgresql Archives

Re: Understanding "seq scans"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux