Postgres Pro
Downloads
Home   >   mailing lists

Thread: gin index postgres 9.2

gin index postgres 9.2

From
Lucas Possamai
Date:

I've got the following query:

SELECT COUNT(DISTINCT j0_.id) AS sclr0
FROM ja_customers j0_
WHERE ((LOWER(j0_.name_first) LIKE '%asd%'       OR LOWER(j0_.name_last) LIKE '%asd%'       OR LOWER(j0_.company) LIKE '%asd%'       OR LOWER(j0_.phone) LIKE '%asd%'       OR LOWER(j0_.mobile) LIKE '%asd%')      AND j0_.deleted = 'f'      AND j0_.clientid = 2565) AND j0_.clientid = 2565

It returns: 3

I created a GIN index;

CREATE INDEX CONCURRENTLY ON public.ja_customers USING gin (name_first gin_trgm_ops, name_last gin_trgm_ops, company gin_trgm_ops, phone gin_trgm_ops, mobile gin_trgm_ops);

New query to hit the new index:

SELECT COUNT(DISTINCT j0_.id) AS sclr0
FROM ja_customers j0_
WHERE j0_.name_first LIKE '%asd%'       OR j0_.name_last LIKE '%asd%'       OR j0_.company LIKE '%asd%'       OR j0_.phone LIKE '%asd%'       OR j0_.mobile LIKE '%asd%'      AND j0_.deleted = 'f'      AND j0_.clientid = 2565 AND j0_.clientid = 2565

It returns: 532


The problem is that the new query returns different results...

Please, what am I missing guys?


Cheers

Re: gin index postgres 9.2

From
"David G. Johnston"
Date:
On Wed, May 25, 2016 at 6:34 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:

I've got the following query:

SELECT COUNT(DISTINCT j0_.id) AS sclr0
FROM ja_customers j0_
WHERE ((LOWER(j0_.name_first) LIKE '%asd%'       OR LOWER(j0_.name_last) LIKE '%asd%'       OR LOWER(j0_.company) LIKE '%asd%'       OR LOWER(j0_.phone) LIKE '%asd%'       OR LOWER(j0_.mobile) LIKE '%asd%')      AND j0_.deleted = 'f'      AND j0_.clientid = 2565) AND j0_.clientid = 2565

It returns: 3

I created a GIN index;

CREATE INDEX CONCURRENTLY ON public.ja_customers USING gin (name_first gin_trgm_ops, name_last gin_trgm_ops, company gin_trgm_ops, phone gin_trgm_ops, mobile gin_trgm_ops);

New query to hit the new index:

SELECT COUNT(DISTINCT j0_.id) AS sclr0
FROM ja_customers j0_
WHERE j0_.name_first LIKE '%asd%'       OR j0_.name_last LIKE '%asd%'       OR j0_.company LIKE '%asd%'       OR j0_.phone LIKE '%asd%'       OR j0_.mobile LIKE '%asd%'      AND j0_.deleted = 'f'      AND j0_.clientid = 2565 AND j0_.clientid = 2565

It returns: 532


The problem is that the new query returns different results...

Please, what am I missing guys?

​Parentheses?

David J.
​

Re: gin index postgres 9.2

From
Lucas Possamai
Date:
yes.. I thought too.

the results r still different 

Re: gin index postgres 9.2

From
"David G. Johnston"
Date:
On Wed, May 25, 2016 at 7:38 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
yes.. I thought too.

the results r still different 

​What's the query with the revised parentheses?

David J.
​
 

Re: gin index postgres 9.2

From
"David G. Johnston"
Date:
On Wed, May 25, 2016 at 7:42 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, May 25, 2016 at 7:38 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
yes.. I thought too.

the results r still different 

​What's the query with the revised parentheses?


​Never mind...your right I doubt that it should matter.

What I would do is remove the whole "count(DISTINCT)" part and select * - and then add a LIMIT 10 and an ORDER BY to both the before and after.  If we see the data it might help to explain the behavior.

It could be a bug...but you'd need to provide a self-contained script so that someone else can confirm.

David J.
​
 

Re: gin index postgres 9.2

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, May 25, 2016 at 7:42 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> ​What's the query with the revised parentheses?

> ​Never mind...your right I doubt that it should matter.

The parentheses in the original *definitely* matter, because by default
AND binds more tightly than OR.  The larger number of rows in the second
query are perfectly plausible given the parenthesis-omission.

            regards, tom lane

Re: gin index postgres 9.2

From
"David G. Johnston"
Date:
On Wed, May 25, 2016 at 8:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, May 25, 2016 at 7:42 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> ​What's the query with the revised parentheses?

> ​Never mind...your right I doubt that it should matter.

The parentheses in the original *definitely* matter, because by default
AND binds more tightly than OR.  The larger number of rows in the second
query are perfectly plausible given the parenthesis-omission.

​That's what I get for second-guessing myself...

To be more precise the behavior shown is exhibited in the following three queries.

SELECT true OR false OR false AND false AND false  --> true
SELECT true OR false OR (false AND false AND false) -->true
or, more precisely:
SELECT (true OR false) OR ((false AND false) AND false) -->true

what is thus needed is:
SELECT (true OR false OR false) AND false AND false --> false
which is treated like
SELECT (((true OR false) OR false) AND false) AND false --> false

penance served...

David J.
​