Re: Efficient Correlated Update

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 <kgrittn(at)ymail(dot)com> wrote:

> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>