Slow update with simple query
От | Arnaud Lesauvage |
---|---|
Тема | Slow update with simple query |
Дата | |
Msg-id | 457FDB1E.6040801@freesurf.fr обсуждение исходный текст |
Ответы |
Re: Slow update with simple query
Re: Slow update with simple query |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: