gin index postgres 9.2
I've got the following query:
SELECT COUNT(DISTINCT j0_.id) AS sclr0FROM 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 sclr0FROM 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
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 sclr0FROM 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 = 2565It 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 sclr0FROM 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 = 2565It returns: 532
The problem is that the new query returns different results...
Please, what am I missing guys?
Parentheses?
David J.
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.
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.
"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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE
penance served...
David J.