How to improve UPDATE query execution time in Postgresql?

Question

I have the following query for 3hours +:

UPDATE eop_201007
set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4
from eop_201007 as eop, geoindex201001 as gi
where eop.cp7=gi.cp7 AND eop.gr_type=0;

The eop table has 300k + entries and the gi 100k + table.

The cp7 field is indexed in both tables, and it takes too long to complete.

Am I doing it wrong? How can I improve this?

Answer 1

Check this section and use EXPLAIN to find out what is happening. A better configuration for WAL may also help, just check your memory usage and write speed during the upgrade.

: , , ...

SELECT 
    relname, 
    * 
FROM 
    pg_locks
        JOIN pg_class ON pg_locks.relation  = pg_class.oid

Answer 2

"eop_201007 eop" FROM. :

UPDATE eop_201007
set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4
from geoindex201001 as gi
where eop_201007.cp7=gi.cp7 AND eop_201007.gr_type=0;

, eop ( , , ), eop, "" FROM

, :

, , . , postgresql.conf. , .. .

, , fsync. , , ( fsync), (24 ), .

@Frank , . EXPLAIN ANALYZE ( ).