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

創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì)、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)肅南裕固族自治,10余年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專(zhuān)業(yè),歡迎來(lái)電咨詢建站服務(wù):18980820575

這種情況幾星期前在Heap出現(xiàn)了。我們?cè)贖eap為每個(gè)跟蹤用戶維護(hù)一個(gè)事件數(shù)組，在這個(gè)數(shù)組中我們用一個(gè)hstore datum代表每個(gè)事件。我們有一個(gè)導(dǎo)入管道來(lái)追加新事件到對(duì)應(yīng)的數(shù)組。為了使這一導(dǎo)入管道是冪等的，我們給每個(gè)事件設(shè)定一個(gè)event_id，我們通過(guò)一個(gè)功能函數(shù)重復(fù)運(yùn)行我們的事件數(shù)組。如果我們要更新附加到事件的屬性的話，我們只需使用相同的event_id轉(zhuǎn)儲(chǔ)一個(gè)新的事件到管道中。

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

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

這個(gè)查詢?cè)趽碛?.4GHz的i7CPU及16GB Ram的macbook pro上測(cè)得，運(yùn)行腳本為：https://gist.github.com/drob/9180760。

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

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

相反的是

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

對(duì)于那些長(zhǎng)度不一的變量, 舉個(gè)例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必須去找到每一個(gè)變量的長(zhǎng)度. 對(duì)于evaluateevents[2], PostgreSQL 解析從左側(cè)讀取的事件直到讀取到第二次讀取的數(shù)據(jù). 然后就是 forevents[3], 她再一次的從第一個(gè)索引處開(kāi)始掃描,直到讀到第三次的數(shù)據(jù)! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]對(duì)于在數(shù)組中的每一個(gè)索引都是 O(N2), N是數(shù)組的長(zhǎng)度.

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

即便如此，我們也不應(yīng)該讓PostgreSQL來(lái)處理，因?yàn)檫@不是一個(gè)地道的查詢。除了generate_subscripts我們可以用unnest，它解析數(shù)組并返回一組條目。這樣一來(lái)，我們就不需要在數(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;

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

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

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

•     按event_id劃分。

•     對(duì)每個(gè)event_id采用最新出現(xiàn)的。

•     按輸入索引排序。

教訓(xùn)：如果你需要訪問(wè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各元素時(shí)它需要大約40秒！

這個(gè)查詢?cè)趽碛?.4GHz的i7CPU及16GB Ram的macbook pro上測(cè)得，運(yùn)行腳本為：https://gist.github.com/drob/9180760。

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

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

相反的是

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

對(duì)于那些長(zhǎng)度不一的變量, 舉個(gè)例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必須去找到每一個(gè)變量的長(zhǎng)度. 對(duì)于evaluateevents[2], PostgreSQL 解析從左側(cè)讀取的事件直到讀取到第二次讀取的數(shù)據(jù). 然后就是 forevents[3], 她再一次的從第一個(gè)索引處開(kāi)始掃描,直到讀到第三次的數(shù)據(jù)! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]對(duì)于在數(shù)組中的每一個(gè)索引都是 O(N2), N是數(shù)組的長(zhǎng)度.

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

即便如此，我們也不應(yīng)該讓PostgreSQL來(lái)處理，因?yàn)檫@不是一個(gè)地道的查詢。除了generate_subscripts我們可以用unnest，它解析數(shù)組并返回一組條目。這樣一來(lái)，我們就不需要在數(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;

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

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

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

•     按event_id劃分。

•     對(duì)每個(gè)event_id采用最新出現(xiàn)的。

•     按輸入索引排序。

關(guān)于PostgreSQL中如何使用數(shù)組問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

網(wǎng)頁(yè)名稱：PostgreSQL中如何使用數(shù)組
轉(zhuǎn)載注明：http://chinadenli.net/article6/pgjhog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計(jì)、云服務(wù)器、小程序開(kāi)發(fā)、網(wǎng)站改版、企業(yè)建站、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)