pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow update
18+ messages / 11 participants
[nested] [flat]
* Slow update
@ 2005-04-12 14:12 Bendik R.Johansen <bendik.johansen@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Bendik R.Johansen @ 2005-04-12 14:12 UTC (permalink / raw)
To: pgsql-performance
Hello,
I am having a bit of trouble updating a single integer column.
My table has around 10 columns and 260 000 records.
update no.records set uid = 2;
(uid is an integer. It has a btree index)
This update takes more than 20 minutes to execute. Is this normal? This
will be totally unacceptable when my table grows.
Any ideas?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-04-12 14:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bendik R.Johansen <bendik.johansen@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2005-04-12 14:35 UTC (permalink / raw)
To: Bendik R.Johansen <bendik.johansen@gmail.com>; +Cc: pgsql-performance
"Bendik R.Johansen" <bendik.johansen@gmail.com> writes:
> I am having a bit of trouble updating a single integer column.
> My table has around 10 columns and 260 000 records.
> update no.records set uid = 2;
> (uid is an integer. It has a btree index)
> This update takes more than 20 minutes to execute. Is this normal?
Takes about 20 seconds to update a table of that size on my machine...
What PG version is this? We used to have some performance issues with
very large numbers of equal keys in btree indexes. Does dropping the
index make it faster?
Are there foreign keys referencing this table from other tables?
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-04-12 15:03 Bendik R.Johansen <bendik.johansen@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Bendik R.Johansen @ 2005-04-12 15:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hello, thank you for the quick reply.
I am running version 8.0.1
Below is the schema for the table i will be using. I tried dropping the
index, but it did not help.
Table "no.records"
Column | Type |
Modifiers
-------------+--------------------------
+-------------------------------------------------------
id | integer | not null default
nextval('"no".records_id_seq'::text)
origid | integer |
cid | character varying(16) | default ''::character varying
category | integer[] |
name | character varying(255) | not null default
''::character varying
address | character varying(128) |
street | character varying(127) |
postalcode | integer |
postalsite | character varying(64) |
email | character varying(64) |
website | character varying(64) |
phone | character varying(16) |
fax | character varying(16) |
contact | character varying(64) |
info | text |
position | point |
importid | integer |
exportid | integer |
created | timestamp with time zone |
creator | integer |
updated | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
updater | integer |
uid | integer |
relevance | real | not null default 0
phonetic | text |
uncertainty | integer | default 99999999
indexed | boolean | default false
record | text |
Indexes:
"records_pkey" PRIMARY KEY, btree (id)
"records_category_idx" gist (category)
"records_cid_idx" btree (cid)
"records_uid_idx" btree (uid)
On Apr 12, 2005, at 16:35, Tom Lane wrote:
> "Bendik R.Johansen" <bendik.johansen@gmail.com> writes:
>> I am having a bit of trouble updating a single integer column.
>> My table has around 10 columns and 260 000 records.
>
>> update no.records set uid = 2;
>> (uid is an integer. It has a btree index)
>
>> This update takes more than 20 minutes to execute. Is this normal?
>
> Takes about 20 seconds to update a table of that size on my machine...
>
> What PG version is this? We used to have some performance issues with
> very large numbers of equal keys in btree indexes. Does dropping the
> index make it faster?
>
> Are there foreign keys referencing this table from other tables?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-04-12 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bendik R.Johansen <bendik.johansen@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2005-04-12 15:16 UTC (permalink / raw)
To: Bendik R. Johansen <bendik.johansen@gmail.com>; +Cc: pgsql-performance
"Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
> Below is the schema for the table i will be using. I tried dropping the
> index, but it did not help.
> Indexes:
> "records_pkey" PRIMARY KEY, btree (id)
> "records_category_idx" gist (category)
> "records_cid_idx" btree (cid)
> "records_uid_idx" btree (uid)
Hmm ... my suspicion would fall first on the GIST index, to tell you the
truth. Did you try dropping that one?
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-04-12 15:37 Bendik R.Johansen <bendik.johansen@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Bendik R.Johansen @ 2005-04-12 15:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Yes, I tried dropping it but it did not make a difference.
Could the table be corrupt or something?
Well, the important thing is that I now know that this is not typical
for PostgreSQL, so I will not have to rethink my whole project.
Thanks, so far.
On Apr 12, 2005, at 17:16, Tom Lane wrote:
> "Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
>> Below is the schema for the table i will be using. I tried dropping
>> the
>> index, but it did not help.
>
>> Indexes:
>> "records_pkey" PRIMARY KEY, btree (id)
>> "records_category_idx" gist (category)
>> "records_cid_idx" btree (cid)
>> "records_uid_idx" btree (uid)
>
> Hmm ... my suspicion would fall first on the GIST index, to tell you
> the
> truth. Did you try dropping that one?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-04-12 15:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bendik R.Johansen <bendik.johansen@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2005-04-12 15:40 UTC (permalink / raw)
To: Bendik R. Johansen <bendik.johansen@gmail.com>; +Cc: pgsql-performance
"Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
> Yes, I tried dropping it but it did not make a difference.
> Could the table be corrupt or something?
You didn't directly answer the question about whether there were foreign
keys leading to this table. Checking foreign keys could be the problem,
particularly if the referencing columns don't have indexes.
Also, maybe the table is just bloated? What does VACUUM VERBOSE say
about it?
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Slow update
@ 2005-09-12 09:14 Hilary Forbes <hforbes@dmr.co.uk>
0 siblings, 3 replies; 18+ messages in thread
From: Hilary Forbes @ 2005-09-12 09:14 UTC (permalink / raw)
To: pgsql-performance
Hello everyone
I must be doing something very wrong here so help please! I have two tables
tableA has 300,000 recs
tableB has 20,000 recs
I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is
UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1;
The primary key of tableA is key1 and that of tableB is key1 ie the join is on primary keys.
The "optimizer" has elected to d a sequential scan on tableA to determine which fields to update rather than the query being driveb by tableB and it is taking forever. Surely I must be able to force the system to read down tableB in preference to reading down tableA?
(Please don't ask why tableA and tableB are not amalgamated - that's another story altogether!!!)
Many thanks in advance
Hilary
Hilary Forbes
The DMR Information and Technology Group (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-09-12 10:51 Richard Huxton <dev@archonet.com>
parent: Hilary Forbes <hforbes@dmr.co.uk>
2 siblings, 0 replies; 18+ messages in thread
From: Richard Huxton @ 2005-09-12 10:51 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
Hilary Forbes wrote:
>
> I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is
>
> UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1;
Check the EXPLAIN carefully, are you sure the tableA in "UPDATE" is the
same as that in your "FROM" clause. If so, why are you SETting a.val1?
If not, you've probably got an unconstrained join.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-09-12 13:34 Bruno Wolff III <bruno@wolff.to>
parent: Hilary Forbes <hforbes@dmr.co.uk>
2 siblings, 0 replies; 18+ messages in thread
From: Bruno Wolff III @ 2005-09-12 13:34 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
On Mon, Sep 12, 2005 at 10:14:25 +0100,
Hilary Forbes <hforbes@dmr.co.uk> wrote:
> Hello everyone
>
> I must be doing something very wrong here so help please! I have two tables
>
> tableA has 300,000 recs
> tableB has 20,000 recs
>
> I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is
>
> UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1;
>
> The primary key of tableA is key1 and that of tableB is key1 ie the join is on primary keys.
>
> The "optimizer" has elected to d a sequential scan on tableA to determine which fields to update rather than the query being driveb by tableB and it is taking forever. Surely I must be able to force the system to read down tableB in preference to reading down tableA?
It would help to see the exact query and the explain analyze output. Hopefully
you didn't really write the query similar to above, since it is using illegal
syntax and the if it was changed slightly to become legal than it would do a
cross join of table A with the inner join of tableA and tableB, which isn't
what you want.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2005-09-12 14:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hilary Forbes <hforbes@dmr.co.uk>
2 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2005-09-12 14:07 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
Hilary Forbes <hforbes@dmr.co.uk> writes:
> I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is
> UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1;
You've written an unconstrained join to a second copy of tableA.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* slow update
@ 2005-10-13 16:34 Patrick Hatcher <PHatcher@macys.com>
0 siblings, 1 reply; 18+ messages in thread
From: Patrick Hatcher @ 2005-10-13 16:34 UTC (permalink / raw)
To: pgsql-performance
Pg 7.4.5
RH 7.3
Quad Xeon 3Gz
12G ram
Trying to do a update of fields on 23M row database.
Is it normal for this process to take 16hrs and still clocking? Both join
fields are indexed and I have removed any indexes on the updated columns.
Also both tables are vacuumed regularly.
I'm weary to cancel the job for fear that it is just slow and I'll have to
repeat the 16hr job.
Any suggestions of what I can check for the bottleneck?
Below is my update statement and table structure:
update cdm.cdm_ddw_tran_item
set dept_id = dept,
vend_id = vend,
mkstyl = mstyle
from flbasics
where flbasics.upc = cdm.cdm_ddw_tran_item.item_upc;
CREATE TABLE cdm.cdm_ddw_tran_item
(
appl_xref varchar(22),
intr_xref varchar(13),
tran_typ_id char(1),
tran_ship_amt numeric(8,2),
fill_store_div int4,
soldto_cust_id int8,
soldto_cust_seq int4,
shipto_cust_id int8,
shipto_cust_seq int4,
itm_qty int4,
itm_price numeric(8,2),
item_id int8,
item_upc int8,
item_pid varchar(20),
item_desc varchar(30),
nrf_color_name varchar(10),
nrf_size_name varchar(10),
dept_id int4,
vend_id int4,
mkstyl int4,
ddw_tran_key bigserial NOT NULL,
price_type_id int2 DEFAULT 999,
last_update date DEFAULT ('now'::text)::date,
CONSTRAINT ddw_tritm_pk PRIMARY KEY (ddw_tran_key)
)
WITHOUT OIDS;
CREATE TABLE flbasics
(
upc int8,
dept int4,
vend int4,
mstyle int4,
xcolor int4,
size int4,
owned float8,
cost float8,
xclass int2,
firstticket float8,
status char(2),
last_receipt date,
description varchar(50),
pack_qty int2,
discontinue_date date,
std_rcv_units int4,
std_rcv_cost float8,
std_rcv_retail float8,
first_receipt date,
last_pchange varchar(9),
ticket float8,
std_mkd_units int4,
std_mkd_dollars float8
)
WITHOUT OIDS;
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: slow update
@ 2005-10-13 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patrick Hatcher <PHatcher@macys.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2005-10-13 18:34 UTC (permalink / raw)
To: Patrick Hatcher <PHatcher@macys.com>; +Cc: pgsql-performance
Patrick Hatcher <PHatcher@macys.com> writes:
> Pg 7.4.5
> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?
Are there foreign keys pointing at the table being updated? If so,
failure to index the referencing columns could create this sort of
performance problem. Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: slow update
@ 2005-10-13 18:37 Patrick Hatcher <PHatcher@macys.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 18+ messages in thread
From: Patrick Hatcher @ 2005-10-13 18:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks. No foreign keys and I've been bitten by the mismatch datatypes and
checked that before sending out the message :)
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
Tom Lane
<tgl@sss.pgh.pa.u
s> To
Patrick Hatcher
10/13/2005 11:34 <PHatcher@macys.com>
AM cc
postgres performance list
<pgsql-performance@postgresql.org>
Subject
Re: [PERFORM] slow update
Patrick Hatcher <PHatcher@macys.com> writes:
> Pg 7.4.5
> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?
Are there foreign keys pointing at the table being updated? If so,
failure to index the referencing columns could create this sort of
performance problem. Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Slow update
@ 2007-02-01 00:57 Abu Mushayeed <abumushayeed@yahoo.com>
0 siblings, 1 reply; 18+ messages in thread
From: Abu Mushayeed @ 2007-02-01 00:57 UTC (permalink / raw)
To: pgsql-performance
Following is one of the update query and it's explain plan which takes about 6 mins to execute. I am trying to find a way to execute it faster. The functions used in the update statement are if then else test and then return one value or the other.
=====================================================
update mdc_upc
set total_curoh = ownedgoods(k.gmmid,k.divid, loc1_oh, loc2_oh,loc3_oh,loc120_oh, loc15_oh,chesh_curoh),
total_curoo =orderedgoods(k.gmmid,k.divid, loc1_oo, loc2_oo,loc3_oo,loc120_oo, loc15_oo,chesh_curoo),
total_oh = ownedgoods(k.gmmid,k.divid, 0, 0,loc3_oh,loc120_oh, loc15_oh,chesh_oh),
total_oo =orderedgoods(k.gmmid,k.divid, 0, 0,loc3_oo,loc120_oo, loc15_oo,chesh_oo)
from mdc_products p LEFT OUTER JOIN
kst k on p.dvm_d = k.dept
where p.keyp_products = mdc_upc.keyf_products;
---------------------------------------------------------------------------
Hash Join (cost=48602.07..137331.77 rows=695899 width=391)
Hash Cond: ("outer".keyf_products = "inner".keyp_products)
-> Seq Scan on mdc_upc (cost=0.00..59153.99 rows=695899 width=383)
-> Hash (cost=47274.60..47274.60 rows=530990 width=12)
-> Hash Left Join (cost=43.85..47274.60 rows=530990 width=12)
Hash Cond: ("outer".dvm_d = "inner".dept)
-> Seq Scan on mdc_products p (cost=0.00..39265.90 rows=530990 width=8)
-> Hash (cost=41.48..41.48 rows=948 width=12)
-> Seq Scan on kst k (cost=0.00..41.48 rows=948 width=12)
======================================================
I have seen that the updates are very slow on our system. What parameter should I test in order to find out why is it slow during update.
Thanks
Abu
---------------------------------
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Slow update
@ 2007-02-01 01:32 Tomas Vondra <tv@fuzzy.cz>
parent: Abu Mushayeed <abumushayeed@yahoo.com>
0 siblings, 0 replies; 18+ messages in thread
From: Tomas Vondra @ 2007-02-01 01:32 UTC (permalink / raw)
To: Abu Mushayeed <abumushayeed@yahoo.com>; +Cc: pgsql-performance
> Following is one of the update query and it's explain plan which takes
> about 6 mins to execute. I am trying to find a way to execute it faster.
> The functions used in the update statement are if then else test and
> then return one value or the other.
> =====================================================
> update mdc_upc
> set total_curoh = ownedgoods(k.gmmid,k.divid, loc1_oh,
> loc2_oh,loc3_oh,loc120_oh, loc15_oh,chesh_curoh),
> total_curoo =orderedgoods(k.gmmid,k.divid, loc1_oo,
> loc2_oo,loc3_oo,loc120_oo, loc15_oo,chesh_curoo),
> total_oh = ownedgoods(k.gmmid,k.divid, 0, 0,loc3_oh,loc120_oh,
> loc15_oh,chesh_oh),
> total_oo =orderedgoods(k.gmmid,k.divid, 0, 0,loc3_oo,loc120_oo,
> loc15_oo,chesh_oo)
> from mdc_products p LEFT OUTER JOIN
> kst k on p.dvm_d = k.dept
> where p.keyp_products = mdc_upc.keyf_products;
>
---------------------------------------------------------------------------
> Hash Join (cost=48602.07..137331.77 rows=695899 width=391)
> Hash Cond: ("outer".keyf_products = "inner".keyp_products)
> -> Seq Scan on mdc_upc (cost=0.00..59153.99 rows=695899 width=383)
> -> Hash (cost=47274.60..47274.60 rows=530990 width=12)
> -> Hash Left Join (cost=43.85..47274.60 rows=530990 width=12)
> Hash Cond: ("outer".dvm_d = "inner".dept)
> -> Seq Scan on mdc_products p (cost=0.00..39265.90
> rows=530990 width=8)
> -> Hash (cost=41.48..41.48 rows=948 width=12)
> -> Seq Scan on kst k (cost=0.00..41.48 rows=948
> width=12)
>
> ======================================================
> I have seen that the updates are very slow on our system. What parameter
> should I test in order to find out why is it slow during update.
>
> Thanks
> Abu
Obviously the update is slow because of sequential scans of the table,
with about 1GB od data to read - this may seem as 'not too much of data'
but there may be a lot of seeks, knocking the performance down. You can
use iowait / dstat to see how much time is spent waiting for the data.
I have to admin I don't fully uderstand that query as I've never used
the UPDATE ... FROM query, but it seems to me you could set up some
indexes to speed things up. I'd definitely start with indexes on the
columns used in join conditions, namely
CREATE INDEX mdc_products_keyp_idx ON mdc_products(keyp_products);
CREATE INDEX mdc_upc_keyf_idx ON mdc_upc(keyf_products);
CREATE INDEX mdc_products_dvm_idx ON mdc_products(dvm_d);
CREATE INDEX kst_dept_idx ON kst(dept);
but this is just a guess as I really know NOTHING about those tables
(structure, size, statistical features, etc.) Btw. don't forget to
analyze the tables.
Another 'uncertainty' is related to the functions used in your query,
namely ownedgoods() / orderedgoods(). If these procedures do something
nontrivial (searches in tables, etc.) it might have severe impact on the
query - but the parser / optimizer knows nothing about these procedures
so it can't optimize them.
If this won't help, we'll need some more information - for example what
does the 'our system' mean - how much memory doest it have? What are the
important settings in your postgresql.conf? Especially what is the value
of effective_cache_size, work_mem, and some others (for example number
of checkpoint segments as it seems like a write-intensive query).
And last but not least info of the structure and size of the tables
(columns, indexes, number of rows, number of occupied blocks, etc).
Tomas
^ permalink raw reply [nested|flat] 18+ messages in thread
* slow update
@ 2008-05-20 05:56 kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 2 replies; 18+ messages in thread
From: kevin kempter @ 2008-05-20 05:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: slow update
@ 2008-05-20 06:15 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
1 sibling, 0 replies; 18+ messages in thread
From: A. Kretschmer @ 2008-05-20 06:15 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: slow update
@ 2008-05-20 17:47 Scott Marlowe <scott.marlowe@gmail.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
1 sibling, 0 replies; 18+ messages in thread
From: Scott Marlowe @ 2008-05-20 17:47 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
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));
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2008-05-20 17:47 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-12 14:12 Slow update Bendik R.Johansen <bendik.johansen@gmail.com>
2005-04-12 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-12 15:03 ` Bendik R.Johansen <bendik.johansen@gmail.com>
2005-04-12 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-12 15:37 ` Bendik R.Johansen <bendik.johansen@gmail.com>
2005-04-12 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-12 09:14 Slow update Hilary Forbes <hforbes@dmr.co.uk>
2005-09-12 10:51 ` Richard Huxton <dev@archonet.com>
2005-09-12 13:34 ` Bruno Wolff III <bruno@wolff.to>
2005-09-12 14:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 16:34 slow update Patrick Hatcher <PHatcher@macys.com>
2005-10-13 18:34 ` Re: slow update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 18:37 ` Re: slow update Patrick Hatcher <PHatcher@macys.com>
2007-02-01 00:57 Slow update Abu Mushayeed <abumushayeed@yahoo.com>
2007-02-01 01:32 ` Tomas Vondra <tv@fuzzy.cz>
2008-05-20 05:56 slow update kevin kempter <kevin@kevinkempterllc.com>
2008-05-20 06:15 ` Re: slow update A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-05-20 17:47 ` Re: slow update Scott Marlowe <scott.marlowe@gmail.com>
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