public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeremy Finzel <[email protected]>
To: postgres performance list <[email protected]>
Subject: Indexing an array of two separate columns
Date: Fri, 15 Sep 2017 15:51:01 -0500
Message-ID: <CAMa1XUgw=G+8Tid=O30MScJZv4XCB7Pj3rX0rpoKo+Z0hZsmAQ@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-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!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Indexing an array of two separate columns
In-Reply-To: <CAMa1XUgw=G+8Tid=O30MScJZv4XCB7Pj3rX0rpoKo+Z0hZsmAQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox