Create one query out of two

Поиск
Список
Период
Сортировка
От Robert DiFalco
Тема Create one query out of two
Дата
Msg-id CAAXGW-zQhcidwZRhgsmmECOtNQQJm8xXwsjVq0vi2Wa5iUMeTA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Create one query out of two
Re: Create one query out of two
Список 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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: queries with DISTINCT / GROUP BY giving different plans
Следующее
От: Calvin Dodge
Дата:
Сообщение: Re: Create one query out of two