Re: Efficient Correlated Update

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-
> performance-owner(at)postgresql(dot)org] On Behalf Of Kevin Grittner
> Sent: Friday, August 09, 2013 11:44 AM
> To: Robert DiFalco; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Efficient Correlated Update
>
> 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".
>
> 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