在PostgreSQL中使用數組時值得注意的一些地方_資料庫其它

來源:互聯網
上載者:User

在Heap中,我們依靠PostgreSQL支撐大多數後端繁重的任務,我們儲存每個事件為一個hstore blob,我們為每個跟蹤的使用者維護一個已完成事件的PostgreSQL數組,並將這些事件按時間排序。 Hstore能夠讓我們以靈活的方式附加屬性到事件中,而且事件數目組賦予了我們強大的效能,特別是對於漏鬥查詢,在這些查詢中我們計算不同轉化渠道步驟間的輸出。

在這篇文章中,我們看看那些意外接受大量輸入的PostgreSQL函數,然後以高效,慣用的方式重寫它。

你的第一反應可能是將PostgreSQL中的數組看做像C語言中對等的類似物。你之前可能用過變換陣列位置或切片來操縱資料。不過要小心,在PostgreSQL中不要有這樣的想法,特別是數群組類型是變長的時,比如JSON、文本或是hstore。如果你通過位置來訪問PostgreSQL數組,你會進入一個意想不到的效能暴跌的境地。


這種情況幾星期前在Heap出現了。我們在Heap為每個跟蹤使用者維護一個事件數目組,在這個數組中我們用一個hstore datum代表每個事件。我們有一個匯入管道來追加新事件到對應的數組。為了使這一匯入管道是等冪的,我們給每個事件設定一個event_id,我們通過一個功能函數重複運行我們的事件數目組。如果我們要更新附加到事件的屬性的話,我們只需使用相同的event_id轉儲一個新的事件到管道中。

所以,我們需要一個功能函數來處理hstores數組,並且,如果兩個事件具有相同的event_id時應該使用數組中最近出現的那個。剛開始嘗試這個函數是這樣寫的:
 

-- This is slow, and you don't want to use it!---- Filter an array of events such that there is only one event with each event_id.-- When more than one event with the same event_id is present, take the latest one.CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$ SELECT array_agg(event) FROM (  -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.  SELECT event  FROM (   -- Rank elements with the same event_id by position in the array, descending.

這個查詢在擁有2.4GHz的i7CPU及16GB Ram的macbook pro上測得,運行指令碼為:https://gist.github.com/drob/9180760。


在這邊究竟發生了什麼呢? 關鍵在於PostgreSQL存貯了一個系列的hstores作為數組的值, 而不是指向值的指標. 一個包含了三個hstores的數組看起來像

{“event_id=>1,data=>foo”, “event_id=>2,data=>bar”, “event_id=>3,data=>baz”}

相反的是

{[pointer], [pointer], [pointer]}

 

對於那些長度不一的變數, 舉個例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必須去找到每一個變數的長度. 對於evaluateevents[2], PostgreSQL 解析從左側讀取的事件直到讀取到第二次讀取的資料. 然後就是 forevents[3], 她再一次的從第一個索引處開始掃描,直到讀到第三次的資料! 所以, evaluatingevents[sub]是 O(sub), 並且 evaluatingevents[sub]對於在數組中的每一個索引都是 O(N2), N是數組的長度.

PostgreSQL能得到更加恰當的解析結果,  它可以在這樣的情況下分析該數組一次. 真正的答案是可變長度的元素與指標來實現,以數組的值, 以至於,我們總能夠處理 evaluateevents[i]在不變的時間內.


即便如此,我們也不應該讓PostgreSQL來處理,因為這不是一個地道的查詢。除了generate_subscripts我們可以用unnest,它解析數組並返回一組條目。這樣一來,我們就不需要在數組中顯式加入索引了。
 

-- Filter an array of events such that there is only one event with each event_id.-- When more than one event with the same event_id, is present, take the latest one.CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$ SELECT array_agg(event) FROM (  -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.  SELECT event  FROM (   -- Rank elements with the same event_id by position in the array, descending.   SELECT event, row_number AS index, rank()   OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY row_number DESC)   FROM (    -- Use unnest instead of generate_subscripts to turn an array into a set.    SELECT event, row_number()    OVER (ORDER BY event -> 'time')    FROM unnest(events) AS event   ) unnested_data  ) deduped_events  WHERE rank = 1  ORDER BY index ASC ) to_agg;$$ LANGUAGE SQL IMMUTABLE;

結果是有效,它花費的時間跟輸入數組的大小呈線性關係。對於100K個元素的輸入它需要大約半秒,而之前的實現需要40秒。

這實現了我們的需求:

  •     一次解析數組,不需要unnest。
  •     按event_id劃分。
  •     對每個event_id採用最新出現的。
  •     按輸入索引排序。

教訓:如果你需要訪問PostgreSQL數組的特定位置,考慮使用unnest代替。 

   SELECT events[sub] AS event, sub, rank()   OVER (PARTITION BY (events[sub] -> 'event_id')::BIGINT ORDER BY sub DESC)   FROM generate_subscripts(events, 1) AS sub  ) deduped_events  WHERE rank = 1  ORDER BY sub ASC ) to_agg;$$ LANGUAGE SQL IMMUTABLE;

這樣奏效,但大輸入是效能下降了。這是二次的,在輸入數組有100K各元素時它需要大約40秒!

這個查詢在擁有2.4GHz的i7CPU及16GB Ram的macbook pro上測得,運行指令碼為:https://gist.github.com/drob/9180760。


在這邊究竟發生了什麼呢? 關鍵在於PostgreSQL存貯了一個系列的hstores作為數組的值, 而不是指向值的指標. 一個包含了三個hstores的數組看起來像

{“event_id=>1,data=>foo”, “event_id=>2,data=>bar”, “event_id=>3,data=>baz”}

相反的是

{[pointer], [pointer], [pointer]}

 

對於那些長度不一的變數, 舉個例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必須去找到每一個變數的長度. 對於evaluateevents[2], PostgreSQL 解析從左側讀取的事件直到讀取到第二次讀取的資料. 然後就是 forevents[3], 她再一次的從第一個索引處開始掃描,直到讀到第三次的資料! 所以, evaluatingevents[sub]是 O(sub), 並且 evaluatingevents[sub]對於在數組中的每一個索引都是 O(N2), N是數組的長度.

PostgreSQL能得到更加恰當的解析結果,  它可以在這樣的情況下分析該數組一次. 真正的答案是可變長度的元素與指標來實現,以數組的值, 以至於,我們總能夠處理 evaluateevents[i]在不變的時間內.


即便如此,我們也不應該讓PostgreSQL來處理,因為這不是一個地道的查詢。除了generate_subscripts我們可以用unnest,它解析數組並返回一組條目。這樣一來,我們就不需要在數組中顯式加入索引了。
 

-- Filter an array of events such that there is only one event with each event_id.-- When more than one event with the same event_id, is present, take the latest one.CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$ SELECT array_agg(event) FROM (  -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.  SELECT event  FROM (   -- Rank elements with the same event_id by position in the array, descending.   SELECT event, row_number AS index, rank()   OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY row_number DESC)   FROM (    -- Use unnest instead of generate_subscripts to turn an array into a set.    SELECT event, row_number()    OVER (ORDER BY event -> 'time')    FROM unnest(events) AS event   ) unnested_data  ) deduped_events  WHERE rank = 1  ORDER BY index ASC ) to_agg;$$ LANGUAGE SQL IMMUTABLE;

結果是有效,它花費的時間跟輸入數組的大小呈線性關係。對於100K個元素的輸入它需要大約半秒,而之前的實現需要40秒。

這實現了我們的需求:

  •     一次解析數組,不需要unnest。
  •     按event_id劃分。
  •     對每個event_id採用最新出現的。
  •     按輸入索引排序。

教訓:如果你需要訪問PostgreSQL數組的特定位置,考慮使用unnest代替。

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.