From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Efficient Correlated Update |
Date: | 2013-08-08 22:21:31 |
Message-ID: | CAAXGW-w7EATqcRcE0PQ6YV4tnTZGyvxnTLHSa2-Y+J=YZEPkwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Guys, let me know if I have not provided enough information on this post.
Thanks!
On Thu, Aug 8, 2013 at 11:06 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>wrote:
> In my system a user can have external contacts. When I am bringing in
> external contacts I want to correlate any other existing users in the
> system with those external contacts. A users external contacts may or may
> not be users in my system. I have a user_id field in "contacts" that is
> NULL if that contact is not a user in my system
>
> Currently I do something like this after reading in external contacts:
>
> UPDATE contacts SET user_id = u.id
> FROM my_users u
> JOIN phone_numbers pn ON u.phone_significant = pn.significant
> WHERE contacts.owner_id = 7 AND contacts.user_id IS NULL AND
> contacts.id = pn.ref_contact_id;
>
> If any of the fields are not self explanatory let me know. "Significant"
> is just the right 7 most digits of a raw phone number.
>
> I'm more interested in possible improvements to my relational logic than
> the details of the "significant" condition. IOW, I'm start enough to
> optimize the "significant" query but not smart enough to know if this is
> the best approach for the overall correlated UPDATE query. :)
>
> So yeah, is this the best way to update a contact's user_id reference
> based on a contacts phone number matching the phone number of a user?
>
> One detail from the schema -- A contact can have many phone numbers but a
> user in my system will only ever have just one phone number. Hence the JOIN
> to "phone_numbers" versus the column in "my_users".
>
> Thanks.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2013-08-09 15:44:15 | Re: Efficient Correlated Update |
Previous Message | Vik Fearing | 2013-08-08 22:09:17 | Re: subselect requires offset 0 for good performance. |