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@xxxxxxxxxxx>
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
