Skip site navigation (1) Skip section navigation (2)

Re: slow update

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "kevin kempter" <kevin(at)kevinkempterllc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update
Date: 2008-05-20 17:47:13
Message-ID: dcc563d10805201047k7fe49f2cl5975a440811371d5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, May 19, 2008 at 11:56 PM, kevin kempter
<kevin(at)kevinkempterllc(dot)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));

In response to

  • slow update at 2008-05-20 05:56:27 from kevin kempter

pgsql-performance by date

Next:From: kevin kempterDate: 2008-05-20 19:51:45
Subject: improving performance for a delete
Previous:From: Bill MoranDate: 2008-05-20 12:32:25
Subject: Re: Author Wanted

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group