Обсуждение: Create one query out of two

Поиск
Список
Период
Сортировка

Create one query out of two

От
Robert DiFalco
Дата:
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.

Re: Create one query out of two

От
Calvin Dodge
Дата:
What does EXPLAIN show?
 
What proportion of contacts have owner_id=7 and user_id is null?

If it's a large number of contacts, I'd try the following:

create temporary table tusers as
select  coalesce(p.ref_contact_id,e.ref_contact_id) as id, u.id as user_id
from my_users u
    left join phone_number p on on p.significant=u.phone_significant
    left join email_addresses e  on e.email=u.email
where p.ref_contact_id is not null or e.ref_contact_id is not null;

create unique index tusers_idx on tusers(id);

update contacts set user_id=t.user_id
from tusers t
where t.id=contacts.id and contacts.owner=7 and contacts.user_id is null;

If it's a small number of contacts, then it might be worth creating a temporary table of that subset, indexing it, then replacing "where p.ref_contact_id is not null or e.ref_contact_id is not null"  with "where p.ref_contact_id in (select id from TEMPTABLE) or e.ref_contact_id in (select id from TEMPTABLE)"


Calvin Dodge


On Sat, Aug 17, 2013 at 3:19 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
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.

Re: Create one query out of two

От
Kevin Grittner
Дата:
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