PostgreSQL中如何使用數(shù)組

169次閱讀

共計 4711 個字符，預計需要花費 12 分鐘才能閱讀完成。

PostgreSQL 中如何使用數(shù)組，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

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

所以，我們需要一個功能函數(shù)來處理 hstores 數(shù)組，并且，如果兩個事件具有相同的 event_id 時應該使用數(shù)組中最近出現(xiàn)的那個。剛開始嘗試這個函數(shù)是這樣寫的：

-- 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。

在這邊究竟發(fā)生了什么呢? 關鍵在于 PostgreSQL 存貯了一個系列的 hstores 作為數(shù)組的值, 而不是指向值的指針. 一個包含了三個 hstores 的數(shù)組看起來像

{“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 解析從左側讀取的事件直到讀取到第二次讀取的數(shù)據(jù). 然后就是 forevents[3], 她再一次的從第一個索引處開始掃描, 直到讀到第三次的數(shù)據(jù)! 所以, evaluatingevents[sub] 是 O(sub), 并且 evaluatingevents[sub] 對于在數(shù)組中的每一個索引都是 O(N2), N 是數(shù)組的長度.

PostgreSQL 能得到更加恰當?shù)慕馕鼋Y果, 它可以在這樣的情況下分析該數(shù)組一次. 真正的答案是可變長度的元素與指針來實現(xiàn)，以數(shù)組的值, 以至于, 我們總能夠處理 evaluateevents[i] 在不變的時間內.

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

-- 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;

結果是有效的，它花費的時間跟輸入數(shù)組的大小呈線性關系。對于 100K 個元素的輸入它需要大約半秒，而之前的實現(xiàn)需要 40 秒。

這實現(xiàn)了我們的需求：

一次解析數(shù)組，不需要 unnest。

按 event_id 劃分。

對每個 event_id 采用最新出現(xiàn)的。

按輸入索引排序。

教訓：如果你需要訪問 PostgreSQL 數(shù)組的特定位置，考慮使用 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;

這樣奏效，但大輸入是性能下降了。這是二次的，在輸入數(shù)組有 100K 各元素時它需要大約 40 秒！