I sometimes experience that updating smaller sets is more efficient than doing all at once in one transaction (talking about 10000+)
Always make sure the update references can make use of indices On Fri, Aug 9, 2013 at 5:44 PM, Kevin Grittner <kgri...@ymail.com> wrote: > Robert DiFalco <robert.difa...@gmail.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". > > In looking it over, nothing jumped out at me as a problem. Are you > having some problem with it, like poor performance or getting > results different from what you expected? > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
