我有下面的表格，并試圖從第二個表格更新到第一個表格，似乎需要超過 15 分鐘，我在那時殺死了它。
基本上只是嘗試將表中的一個欄位設定為另一個欄位。兩個表都有大約 250 萬行。我們如何優化這個操作？
第一張表：
\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)
第二個表：
\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)
我的操作：
UPDATE table1 mu
SET fiyat = fn.fiyat
FROM table2 AS fn
WHERE mu.satici_id = fn.urun_id AND mu.satici = fn.satici;
uj5u.com熱心網友回復：
這是因為索引。postgres 中的每次更新都被視為重新插入該行，而不管列是否更新，因此所有索引都將重新計算。為了使其更快，洗掉索引或交換到新表會起作用（如果可能的話）。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/380407.html
標籤：数据库 PostgreSQL的 加入