DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=cMx+BKxKjQzSfEdbVRX5/g0QydfL3xT0m9V59SEJc3ntAvxIpypkzExJNRZDrhwss+CAfPbodwoF7P3cQESNCTAHSvs8OJXR6pfMJGknlRCzX0qZDb1wjwmIMjp4sXhVnvDO22pm2godju9lAowKGgRG0FBMJqW5quWP3TZSOgU=
Message-ID: <dcc563d10805201047k7fe49f2cl5975a440811371d5@mail.gmail.com>
Date: Tue, 20 May 2008 11:47:13 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "kevin kempter" <kevin@kevinkempterllc.com>
Subject: Re: slow update
Cc: pgsql-performance@postgresql.org
In-Reply-To: <468CEDC5-0707-4C5F-880F-45A75330731A@kevinkempterllc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <468CEDC5-0707-4C5F-880F-45A75330731A@kevinkempterllc.com>

On Mon, May 19, 2008 at 11:56 PM, kevin kempter
<kevin@kevinkempterllc.com> wrote:
> Hi all;
>
> I have a query that does this:
>
> update tab_x set (inactive_dt, last_update_dt) =
> ((select run_dt from current_run_date), (select run_dt from
> current_run_date))
> where
> cust_id::text || loc_id::text in
> (select cust_id::text || loc_id::text from summary_tab);

I think what you're looking for in the where clause is something like:

where (cust_id, loc_id) in (select cust_id, loc_id from summary_tab);

which should let it compare the native types all at once.  Not sure if
this works on versions before 8.2 or not.

If you MUST use that syntax, then create indexes on them, i.e.:

create index tab_x_multidx on tab_x ((cust_id::text||loc_id::text));
create index summary_tab_x_multidx on summary_tab
((cust_id::text||loc_id::text));