Re: Create one query out of two

Robert DiFalco <robert(dot)difalco(at)gmail(dot)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