Slow update with simple query

Поиск
Список
Период
Сортировка
От Arnaud Lesauvage
Тема Slow update with simple query
Дата
Msg-id 457FDB1E.6040801@freesurf.fr
обсуждение исходный текст
Ответы Re: Slow update with simple query  (Ragnar <gnari@hive.is>)
Re: Slow update with simple query  ("Jens Schipkowski" <jens.schipkowski@apus.co.at>)
Список 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 по дате отправления:

Предыдущее
От: Sven Geisler
Дата:
Сообщение: Re: New to PostgreSQL, performance considerations
Следующее
От: Ragnar
Дата:
Сообщение: Re: Slow update with simple query