Efficient Correlated Update

From: Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Efficient Correlated Update
Date: 2013-08-08 18:06:41
Message-ID: CAAXGW-wEWhVvnDshk=z34VqHuEqpNd43XKNAo1b=L5JCVoDbVw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Claudio Freire 2013-08-08 20:08:52 Re: Efficiently query for the most recent record for a given user
Previous Message slapo 2013-08-08 06:59:42 Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.