MIME-Version: 1.0
From: Jeremy Finzel <finzelj@gmail.com>
Date: Fri, 15 Sep 2017 15:51:01 -0500
Message-ID: 
 <CAMa1XUgw=G+8Tid=O30MScJZv4XCB7Pj3rX0rpoKo+Z0hZsmAQ@mail.gmail.com>
Subject: Indexing an array of two separate columns
To: postgres performance list <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="089e08215428fa19c20559408c7c"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--089e08215428fa19c20559408c7c
Content-Type: text/plain; charset="UTF-8"

I have a user who is trying to match overlapping duplicate phone info but
for different customer_ids.

The intended conditional could be expressed:
IF the intersection of the sets
{c.main_phone, c.secondary_phone}
and
{c1.main_phone, c1.secondary_phone}
is not empty
THEN join
EXCEPT where the intersection of the sets =
{'0000000000'}

He wants a join like this:

FROM customers c
INNER JOIN customers c1 on (array[c.main_phone, c.secondary_phone] &&
 array[nullif(c1.main_phone, '0000000000') , nullif(c1.secondary_phone,
'0000000000')])
(array[c.main_phone, c.secondary_phone] && array[nullif(c1.main_phone,
'0000000000') , nullif(c1.secondary_phone, '0000000000')])
WHERE c.customer_id = 1;

I want to index this part:
array[nullif(c1.main_phone, '0000000000') , nullif(c1.secondary_phone,
'0000000000')]

First of all I see I can't create a btree index on an array.  And with
btree_gin, this index is not being used:

CREATE INDEX ON customers USING gin ((NULLIF(main_phone,
'0000000000'::text)), (NULLIF(secondary_phone, '0000000000'::text)));

What am I missing here?  Is there a way to support a condition like this?

Thank you!

--089e08215428fa19c20559408c7c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I have a user who is trying to match overlapping duplicate=
 phone info but for different customer_ids. =C2=A0<div><br></div><div><div>=
The intended conditional could be expressed:=C2=A0</div><div>IF the interse=
ction of the sets</div><div>{c.main_phone, c.secondary_phone}</div><div>and=
</div><div>{c1.main_phone, c1.secondary_phone}</div><div>is not empty=C2=A0=
</div><div>THEN join=C2=A0</div><div>EXCEPT where the intersection of the s=
ets =3D</div><div>{&#39;0000000000&#39;}</div></div><div><br></div><div>He =
wants a join like this:<div><br></div><div>FROM customers c</div><div>INNER=
 JOIN customers c1 on (array[c.main_phone, c.secondary_phone] &amp;&amp; =
=C2=A0array[nullif(c1.main_phone, &#39;0000000000&#39;) , nullif(c1.seconda=
ry_phone, &#39;0000000000&#39;)])<br></div><div>(array[c.main_phone, c.seco=
ndary_phone] &amp;&amp; array[nullif(c1.main_phone, &#39;0000000000&#39;) ,=
 nullif(c1.secondary_phone, &#39;0000000000&#39;)])<br></div><div>WHERE c.c=
ustomer_id =3D 1;</div><div><br></div><div>I want to index this part:</div>=
<div>array[nullif(c1.main_phone, &#39;0000000000&#39;) , nullif(c1.secondar=
y_phone, &#39;0000000000&#39;)]<br></div><div><br></div><div>First of all I=
 see I can&#39;t create a btree index on an array.=C2=A0 And with btree_gin=
, this index is not being used:</div><div><br></div><div>CREATE INDEX ON cu=
stomers USING gin ((NULLIF(main_phone, &#39;0000000000&#39;::text)), (NULLI=
F(secondary_phone, &#39;0000000000&#39;::text)));<br></div><div><br></div><=
div>What am I missing here?=C2=A0 Is there a way to support a condition lik=
e this?</div><div><br></div><div>Thank you!</div></div></div>

--089e08215428fa19c20559408c7c--