DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=LfsDg/KhwY6i3z9HlU/2osqtn06JVhrhLwlkEyZ+VJGJlfOYSnkDu/MQGDwh1hK1m9YQ+pJgxYO4nQmBVeAt0FdqUAdR/DxdQl07uiCiGjnpEtzaE+dKTkCNlLgu7bJE4QzD0xjhqpzZvYVIAbdGeoMaegVJs06TiK8gwDYPpms=;
References: 
 <CAAXGW-zQhcidwZRhgsmmECOtNQQJm8xXwsjVq0vi2Wa5iUMeTA@mail.gmail.com>
Message-ID: <1376857421.73679.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Sun, 18 Aug 2013 13:23:41 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Create one query out of two
To: Robert DiFalco <robert.difalco@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAAXGW-zQhcidwZRhgsmmECOtNQQJm8xXwsjVq0vi2Wa5iUMeTA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Robert DiFalco <robert.difalco@gmail.com> wrote:>

> UPDATE contacts SET user_id =3D u.id
>=A0=A0=A0 FROM my_users u=20
>=A0=A0=A0 JOIN phone_numbers pn ON u.phone_significant =3D pn.significant=
=20
>=A0=A0=A0 WHERE contacts.owner_id =3D 7 AND contacts.user_id IS NULL
>=A0=A0=A0 AND contacts.id =3D pn.ref_contact_id;
>
> UPDATE contacts SET user_id =3D u.id
>=A0=A0=A0 FROM my_users u=20
>=A0=A0=A0 JOIN email_addresses em ON u.email =3D em.email=20
>=A0=A0=A0 WHERE contacts.owner_id =3D 7 AND contacts.user_id IS NULL
>=A0=A0=A0 AND contacts.id =3D 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
=A0 ON contacts (owner_id)
=A0 WHERE user_id IS NULL;

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance