From: Scott Marlowe <scott.marlowe@gmail.com>
To: kevin kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow update
Date: Tue, 20 May 2008 11:47:13 -0600
Message-ID: <dcc563d10805201047k7fe49f2cl5975a440811371d5@mail.gmail.com> (raw)
In-Reply-To: <468CEDC5-0707-4C5F-880F-45A75330731A@kevinkempterllc.com>
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));
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, kevin@kevinkempterllc.com
Subject: Re: slow update
In-Reply-To: <dcc563d10805201047k7fe49f2cl5975a440811371d5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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