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

Re: slow update

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update
Date: 2008-05-20 06:15:48
Message-ID: 20080520061548.GA29403@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

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

pgsql-performance by date

Next:From: Markus FeilnerDate: 2008-05-20 11:21:06
Subject: Author Wanted
Previous:From: kevin kempterDate: 2008-05-20 05:56:27
Subject: slow update

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