postgres update with join slow performance-CodePudding

I have below tables and trying to do an update from second table to first one, it seems to take more than 15 minutes and I killed it at that point.

Basically just trying to set one field from a table to another field. Both tables have around 2.5 million rows. How can we optimize this operation?

first table:

\d table1
                              Table "public.fa_market_urunu"
    Column    |            Type             | Collation | Nullable |        Default
-------------- ----------------------------- ----------- ---------- -----------------------
 id           | character varying           |           | not null |
 ad           | character varying           |           |          |
 url          | character varying           |           |          |
 image_url    | character varying           |           |          |
 satici_id    | character varying           |           | not null |
 satici       | character varying           |           | not null |
 category_id  | character varying           |           |          |
 date_created | timestamp with time zone    |           | not null | now()
 last_updated | timestamp(3) with time zone |           | not null | now()
 fiyat        | double precision            |           |          |
Indexes:
    "tbl1_pkey" PRIMARY KEY, btree (id)
    "tbl1_satici" UNIQUE, btree (id, satici)
    "tbl1_satici_id" UNIQUE, btree (satici, id)
    "tbl1_satici_id_last_updated" UNIQUE, btree (satici, id, last_updated)
    "tbl1_satici_id_satici_key" UNIQUE CONSTRAINT, btree (satici_id, satici)
    "tbl1_satici_last_updated_id" UNIQUE, btree (satici, last_updated, id)
    "tbl1_last_updated" btree (last_updated)
    "tbl1_satici_category" btree (satici, category_id)
    "tbl1_satici_category_last_updated" btree (satici, category_id, last_updated)
    "tbl1_satici_last_updated" btree (satici, last_updated)

second table:

\d table2
                Table "public.temp_son_fiyat"
 Column  |       Type        | Collation | Nullable | Default
--------- ------------------- ----------- ---------- ---------
 urun_id | character varying |           |          |
 satici  | character varying |           |          |
 fiyat   | double precision  |           |          |
Indexes:
    "ind_u" UNIQUE, btree (urun_id, satici)

My operation:

UPDATE table1 mu
        SET fiyat = fn.fiyat
        FROM table2 AS fn
        WHERE mu.satici_id = fn.urun_id AND mu.satici = fn.satici;

CodePudding user response：

This happens because of the indexes. Every update in postgres is considered as reinsertion of that row regardless of the column getting updated, so all indexes are recalculated. To make it faster, dropping indexes or swapping to a new table would work (if it is possible to do those).