偶然發現個情況
postgresql創建個hash磁區方式的磁區表
執行己指定磁區欄位值的更新陳述句時，執行計劃顯示掃描了所有磁區，正常情況應當只掃對應的那一個磁區才對呀
CREATE TABLE public.wx_friends_new (
friends_id int4 NOT NULL,
merchant_id int4 NOT NULL,
personal_wechat_id int4 NOT NULL,
update_user varchar(512) NOT NULL
) partition by hash(merchant_id);
--創建了50個磁區
create table wx_friends_01 partition of wx_friends_new for values with(modulus 50, remainder 0);
create table wx_friends_02 partition of wx_friends_new for values with(modulus 50, remainder 1);
create table wx_friends_03 partition of wx_friends_new for values with(modulus 50, remainder 2);
create table wx_friends_04 partition of wx_friends_new for values with(modulus 50, remainder 3);
......
--填充資料后，執行
update wx_friends_new set
update_user='FD_191028192115615983_877fdd689a13da43_112_100010'
WHERE merchant_id=100010 and wechat_id='wxid_sb13qm5ygakt22'
執行計劃如圖:
不知是不是Postgresql的BUG還是有什么地方沒弄對？
uj5u.com熱心網友回復：
我也遇到了這個問題。。查了半天，就看到官網這句話“In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a DELETE and INSERT operation.
There is a possibility that a concurrent UPDATE or DELETE on the row being moved will get a serialization failure error. Suppose session 1 is performing an UPDATE on a partition key, and meanwhile a concurrent session 2 for which this row is visible performs an UPDATE or DELETE operation on this row. In such case, session 2's UPDATE or DELETE will detect the row movement and raise a serialization failure error (which always returns with an SQLSTATE code '40001'). Applications may wish to retry the transaction if this occurs. In the usual case where the table is not partitioned, or where there is no row movement, session 2 would have identified the newly updated row and carried out the UPDATE/DELETE on this new row version.”
不過因為沒有更新磁區列，所以我感覺這句話也沒啥用。。可能是沒有做優化。。
我解決這個問題的處理方式就把所有的update操作改為了upsert（insert on conflict）這種。。僅供參考哈
uj5u.com熱心網友回復：
再頂一下，別沉了。uj5u.com熱心網友回復：
之所以掃描了整張表，是因為更新了磁區鍵了吧，因為沒法確認更新前后，更新的記錄磁區是否會發生變更，但實際上執行的時候，應該在每個磁區上消耗的資源不太一樣，可以用explain analyze確認下？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/35030.html
標籤：PostgreSQL