Create one query out of two

From: Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Create one query out of two
Date: 2013-08-17 20:19:10
Message-ID: CAAXGW-zQhcidwZRhgsmmECOtNQQJm8xXwsjVq0vi2Wa5iUMeTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Currently I run two queries back-to-back to correlate users with 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;

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;

For some reason I cannot figure out how to combine these into one update
query. They are running slower than I'd like them to even though I have
indices on user_id, owner_id, email, and significant. So I'd like to try
them in a single query to see if that helps.

As always, thanks for your sage advice.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Calvin Dodge 2013-08-18 00:31:48 Re: Create one query out of two
Previous Message Tomas Vondra 2013-08-16 19:36:19 Re: queries with DISTINCT / GROUP BY giving different plans