如何加快要替換字元、提取某些值並使用結果更新表的字元串操作查詢？

我正在嘗試從以鍵值格式顯示的字元串中提取資訊，鍵和值用逗號分隔。我想提取與某些鍵關聯的值並將它們添加到我表中的專用列中。

關於數據的一些說明：

1. 我感興趣的鍵是連接的，如 keyB 與 keyA 相關；
2. 在某些情況下 keyB 或 keyA 可能不存在
3. 如果 keyB 不存在，但 keyA 是特定的，那麼無論如何我都可以設置 keyB 的值。

我有一個解決方案可以滿足我的需求（$$ db-fiddle $$)，但它非常緩慢（9.6 小時），我不禁想到一定有更好的方法，因為我沒有長時間參與這個 DB 遊戲。

有關資訊，該表有約 820 萬行，並託管在 t3.large DB 上的 AWS RDS 上*（虛擬 CPU = 2，記憶體 = 8.0GB）*。

非常感謝一些關於我可以改進的地方的指示。

主表只需要一個 INSERT 和一個 UPDATE。“變數”（列）的整個預設檢測和填充可以在插入數據時完成：

INSERT INTO main_tags (id, tags, variablea, variableb)
select id, tags, var_a, 
      case 
         when var_b is null and 'valA' = any(tags)  then 'valB_default'
         when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
         else var_b
      end as var_b
from (
 SELECT id, tags, 
        tags[array_position(tags, 'keyA') + 1] as var_a, 
        tags[array_position(tags, 'keyB') + 1] as var_b
 FROM (
   SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
   FROM main
 ) as b 
) x  
WHERE tags && array['keyA','keyB'];

最終更新也可以直接處理“未知”值，無需執行三個更新：

UPDATE main e
 SET variableA = coalesce(et.variableA, 'unknown'),
     variableB = coalesce(et.variableB, 'unknown')
FROM main_tags et
WHERE e.id = et.id;

加快速度的一種方法是在main_tags (id)

---

您實際上可以完全擺脫臨時表並在單個語句中完成所有操作。這可能會或可能不會更快，但我認為值得嘗試：

UPDATE main e
 SET variableA = coalesce(et.var_a, 'unknown'),
     variableB = coalesce(et.var_b, 'unknown')
FROM (
 select id, tags, 
        var_a, 
        case 
           when var_b is null and 'valA' = any(tags)  then 'valB_default'
           when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
           else var_b
        end as var_b
 from (
   SELECT id, 
          tags, 
          tags[array_position(tags, 'keyA') + 1] as var_a, 
          tags[array_position(tags, 'keyB') + 1] as var_b
   FROM (
     SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
     FROM main
   ) as b 
 ) x  
 WHERE tags && array['keyA','keyB']
) as et
WHERE e.id = et.id;

引用自：https://dba.stackexchange.com/questions/273699