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!
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 |