From: Kevin Grittner <kgrittn@ymail.com>
To: Robert DiFalco <robert.difalco@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Efficient Correlated Update
Date: Fri, 9 Aug 2013 08:44:15 -0700 (PDT)
Message-ID: <1376063055.23129.YahooMailNeo@web162902.mail.bf1.yahoo.com> (raw)
In-Reply-To: <CAAXGW-wEWhVvnDshk=z34VqHuEqpNd43XKNAo1b=L5JCVoDbVw@mail.gmail.com>
References: <CAAXGW-wEWhVvnDshk=z34VqHuEqpNd43XKNAo1b=L5JCVoDbVw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Robert DiFalco <robert.difalco@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@ymail.com, robert.difalco@gmail.com
Subject: Re: Efficient Correlated Update
In-Reply-To: <1376063055.23129.YahooMailNeo@web162902.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox