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

Re: Slow update with simple query

From: "Jens Schipkowski" <jens(dot)schipkowski(at)apus(dot)co(dot)at>
To: "Arnaud Lesauvage" <thewild(at)freesurf(dot)fr>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow update with simple query
Date: 2006-12-13 12:18:09
Message-ID: op.tkhy4jpg81rjf6@xjens.apus.local (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

the problem is a combination of bad formed SQL and maybe missing indexes.
try this:
UPDATE t1
SET booleanfield = foo.bar
 FROM (SELECT uid,(field IN ('some','other') AND field2 = 'Y') AS bar FROM  
t2) AS foo
WHERE t1.uid=foo.uid;

and index t1.uid, t2.uid, t2.field, t2.field2

regards,
Jens Schipkowski

On Wed, 13 Dec 2006 11:51:10 +0100, Arnaud Lesauvage <thewild(at)freesurf(dot)fr>  
wrote:

> Hi list !
>
> I am running a query to update the boolean field of a table based on
> another table's fields.
>
> The query is (changed names for readability):
> UPDATE t1
> SET booleanfield = (t2.field1 IN ('some', 'other') AND t2.field2 = 'Y')
> FROM t2
> WHERE t1.uid = t2.uid
>
> t2.uid is the PRIMARY KEY.
> t2 only has ~1000 rows, so I think it fits fully in memory.
> t1 as ~2.000.000 rows.
> There is an index on t1.uid also.
>
> The explain (sorry, not explain analyze available yet) is :
>
> Hash Join  (cost=112.75..307410.10 rows=2019448 width=357)
>    Hash Cond: ("outer".uid= "inner".uid)
>    ->  Seq Scan on t1 (cost=0.00..261792.01 rows=2033001 width=340)
>    ->  Hash  (cost=110.20..110.20 rows=1020 width=53)
>          ->  Seq Scan on t2  (cost=0.00..110.20 rows=1020 width=53)
>
> My query has been running for more than 1.5 hour now, and it is still  
> running.
> Nothing else is running on the server.
> There are two multicolumn-indexes on this column (both are 3-columns  
> indexes). One of them has a functional column (date_trunc('month',  
> datefield)).
>
> Do you think the problem is with the indexes ?
>
> The hardware is not great, but the database is on a RAID1 array, so its  
> not bad either.
> I am surprised that it takes more than 3 seconds per row to be updated.
>
> Thanks for your opinion on this !
>
> --
> Arnaud
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org



-- 
**
APUS Software GmbH

In response to

Responses

pgsql-performance by date

Next:From: Arnaud LesauvageDate: 2006-12-13 12:23:41
Subject: Re: Slow update with simple query
Previous:From: Arnaud LesauvageDate: 2006-12-13 11:35:04
Subject: Re: Slow update with simple query

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