pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate one query out of two
3+ messages / 3 participants
[nested] [flat]
* Create one query out of two
@ 2013-08-17 20:19 Robert DiFalco <robert.difalco@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Robert DiFalco @ 2013-08-17 20:19 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Create one query out of two
@ 2013-08-18 00:31 Calvin Dodge <caldodge@gmail.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Calvin Dodge @ 2013-08-18 00:31 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance
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.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Create one query out of two
@ 2013-08-18 20:23 Kevin Grittner <kgrittn@ymail.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2013-08-18 20:23 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-08-18 20:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-17 20:19 Create one query out of two Robert DiFalco <robert.difalco@gmail.com>
2013-08-18 00:31 ` Calvin Dodge <caldodge@gmail.com>
2013-08-18 20:23 ` Kevin Grittner <kgrittn@ymail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox