Indexing an array of two separate columns

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Indexing an array of two separate columns
Date: 2017-09-15 20:51:01
Message-ID: CAMa1XUgw=G+8Tid=O30MScJZv4XCB7Pj3rX0rpoKo+Z0hZsmAQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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!

Browse pgsql-performance by date

  From Date Subject
Next Message Mike Broers 2017-09-15 20:59:17 Re: query of partitioned object doesnt use index in qa
Previous Message Tom Lane 2017-09-15 20:42:23 Re: query of partitioned object doesnt use index in qa