Robert DiFalco <robert.difalco@gmail.com> wrote:>
> 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;
>
> UPDATE contacts SET user_id = u.id
> FROM my_users u
> JOIN email_addresses em ON u.email = em.email
> WHERE contacts.owner_id = 7 AND contacts.user_id IS NULL
> AND contacts.id = em.ref_contact_id;
>
> They are running slower than I'd like them to even though I have
> indices on user_id, owner_id, email, and significant.
Have you tried those queries with an index like this?:
CREATE INDEX contacts_owner_null_user
ON contacts (owner_id)
WHERE user_id IS NULL;
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company