Giuseppe Broccolo wrote:
> I'm not sure about the '%' operator, but I'm sure that the GIST
> index will never be used in the
>
> SELECT * FROM users WHERE lower(name) LIKE '%john%';
>
> query; it is used for left or right anchored search, such as
> 'john%' or '%john'.
It *will* use a *trigram*
Jeff, I'm not seeing that limitation.
On Thu, Dec 18, 2014 at 10:33 AM, Jeff Janes wrote:
>
> On Thu, Dec 18, 2014 at 10:00 AM, Giuseppe Broccolo <
> giuseppe.brocc...@2ndquadrant.it> wrote:
>>
>> I'm not sure about the '%' operator, but I'm sure that the GIST index
>> will never be used in the
>
On Thu, Dec 18, 2014 at 10:00 AM, Giuseppe Broccolo <
giuseppe.brocc...@2ndquadrant.it> wrote:
>
> I'm not sure about the '%' operator, but I'm sure that the GIST index will
> never be used in the
>
> SELECT * FROM users WHERE lower(name) LIKE '%john%';
>
> query; it is used for left or right anc
I'm pretty sure '%John%' uses the index.
explain analyze verbose SELECT name FROM wai_users WHERE lower(name) LIKE
'%john%';
QUERY PLAN
--
I'm not sure about the '%' operator, but I'm sure that the GIST index will
never be used in the
SELECT * FROM users WHERE lower(name) LIKE '%john%';
query; it is used for left or right anchored search, such as 'john%' or
'%john'.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL T
I know! I was surprised that % 'John' or % 'JOHN' or even % 'jOhn' all
returned the same result.
Besides readability would there be any technical differences between a GIST
index that is lower or not and using LIKE vs. %?
Thanks!
On Thu, Dec 18, 2014 at 9:18 AM, Tom Lane wrote:
>
> Robert DiFa
Robert DiFalco writes:
> So, for my use case I simply need to search for a case insensitive
> substring. It need not be super exact. It seems like there are two ways I
> can do this:
> CREATE INDEX idx_users_name ON users USING GIST(lower(name) gist_trgm_ops);
> SELECT * FROM users WHERE lower(na
So, for my use case I simply need to search for a case insensitive
substring. It need not be super exact. It seems like there are two ways I
can do this:
CREATE INDEX idx_users_name ON users USING GIST(lower(name) gist_trgm_ops);
SELECT * FROM users WHERE lower(name) LIKE '%john%';
Or I can do it
