Thread: slow update

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);


The current_run_date table has only 1 row in it
the summary_tab table has 0 rows and the tab_x had 450,000 rows

The update takes 45min even though there is no rows to update.
I have a compound index (cust_id, loc_id) on both tables (summary_tab
and tab_x)

How can I speed this up ?


Thanks in advance for any thoughts, suggestions, etc...


/Kevin

am  Mon, dem 19.05.2008, um 23:56:27 -0600 mailte kevin kempter folgendes:
> 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);
>
>
> The current_run_date table has only 1 row in it
> the summary_tab table has 0 rows and the tab_x had 450,000 rows
>
> The update takes 45min even though there is no rows to update.
> I have a compound index (cust_id, loc_id) on both tables (summary_tab
> and tab_x)
>
> How can I speed this up ?

Please show us more details, for instance the data-types for cust_id and
loc_id. Wild guess: these columns are INT-Values and you have an Index.
Okay, but in the quere there is a CAST to TEXT -> Index not used.

Verfify this with EXPLAIN.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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));