> -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql- > performance-owner@xxxxxxxxxxxxxx] On Behalf Of Kevin Grittner > Sent: Friday, August 09, 2013 11:44 AM > To: Robert DiFalco; pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: Efficient Correlated Update > > Robert DiFalco <robert.difalco@xxxxxxxxx> 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 > There is an illness that sometimes strikes database developers/administrators. It is called CTD - Compulsive Tuning Disorder :) Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
