Postgres 對批量載入轉換功能的改進

我定期從 httparchive.org 導入數據。數據是一個 MySQL CSV 導出，我使用 pgloader，它處理這個導出的怪癖（\Nfor NULL）等。我還需要做一些額外的處理以進行規範化：

• 在協議 (http|https) 和主機部分中拆分 url
• 將字元串日期“Mon DD YYYY”轉換為日期對象

目前，我在導入數據時有一些觸發器可以執行此操作，但我正在尋找改進方法，特別是查看是否可以並行執行某些步驟。

我有以下用於提取協議和埠的 CTE：

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )

在本地執行這似乎比tsdebug

這適用於選擇，但作為更新似乎非常慢。

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )
update urls
set 
protocol = parts[1],
host = parts[2] 
from split

另一種方法，尤其是在處理文本源時，會在 URL 進入 Postgres 之前對其進行拆分。

未壓縮的 CSV 為 3526430884 字節，導入大約需要 20 分鐘，無需處理。但這與加工相比是兩倍多。FWIW 我也嘗試過使用外部數據包裝器。但是，即使在使用 CSV（空值、編碼）解決了各種問題之後，這也會導致記憶體錯誤。

在一些幫助下，我設法執行了基準測試並改進了我的觸發器。

CREATE OR REPLACE FUNCTION public.extract_protocol()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE elements text [];
BEGIN
elements := regexp_match(NEW.url, '(https|http)://(.+)/');
NEW.protocol = elements[1];
NEW.host = elements[2];
RETURN NEW;
END;
$function$

現在，這比進行後續更新執行得更快，但兩者都不是限制因素。現在的瓶頸是在將清理後的數據插入主表時索引的成本。我認為我唯一的選擇是權衡插入索引的成本，而不是禁用然後添加它們。

您的 UPDATE 語法會生成表urls與split結果的交叉連接。這本質上是表與自身的交叉連接。

您需要在目標表和源表之間建立某種連接條件。顯而易見的選擇是表的主鍵列。

with split as (
 select pk_column, regexp_match(url, '(https|http)://(.+)/' ) as parts 
 from urls 
)
update urls
 set protocol = s.parts[1],
     host = s.parts[2] 
from split s 
where urls.pk_column = s.pk_column --<< here

我認為您嘗試通過使用 CTE 來避免兩次評估正則表達式會使事情變得更慢，而不是更快。我預計將表與自身連接的成本遠大於兩次評估正則表達式的成本。

所以我認為，你也應該嘗試：

update urls
 set protocol = (regexp_match(url, '(https|http)://(.+)/' ))[1]
     host = (regexp_match(url, '(https|http)://(.+)/' ))[2]

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