欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

在PostgreSQL中使用數(shù)組時值得注意的一些地方

 更新時間:2015年04月21日 15:00:45   投稿:goldensun  
這篇文章主要介紹了在PostgreSQL中使用數(shù)組時值得注意的一些地方,包括如何提高輸入性能,需要的朋友可以參考下

在Heap中,我們依靠PostgreSQL支撐大多數(shù)后端繁重的任務(wù),我們存儲每個事件為一個hstore blob,我們?yōu)槊總€跟蹤的用戶維護一個已完成事件的PostgreSQL數(shù)組,并將這些事件按時間排序。 Hstore能夠讓我們以靈活的方式附加屬性到事件中,而且事件數(shù)組賦予了我們強大的性能,特別是對于漏斗查詢,在這些查詢中我們計算不同轉(zhuǎn)化渠道步驟間的輸出。

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

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


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

所以,我們需要一個功能函數(shù)來處理hstores數(shù)組,并且,如果兩個事件具有相同的event_id時應(yīng)該使用數(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ā)生了什么呢? 關(guān)鍵在于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 解析從左側(cè)讀取的事件直到讀取到第二次讀取的數(shù)據(jù). 然后就是 forevents[3], 她再一次的從第一個索引處開始掃描,直到讀到第三次的數(shù)據(jù)! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]對于在數(shù)組中的每一個索引都是 O(N2), N是數(shù)組的長度.

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


即便如此,我們也不應(yīng)該讓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;

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

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

  •     一次解析數(shù)組,不需要unnest。
  •     按event_id劃分。
  •     對每個event_id采用最新出現(xiàn)的。
  •     按輸入索引排序。

教訓(xù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秒!

2015421145745565.png (668×436)

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


在這邊究竟發(fā)生了什么呢? 關(guān)鍵在于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 解析從左側(cè)讀取的事件直到讀取到第二次讀取的數(shù)據(jù). 然后就是 forevents[3], 她再一次的從第一個索引處開始掃描,直到讀到第三次的數(shù)據(jù)! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]對于在數(shù)組中的每一個索引都是 O(N2), N是數(shù)組的長度.

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


即便如此,我們也不應(yīng)該讓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;

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

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

  •     一次解析數(shù)組,不需要unnest。
  •     按event_id劃分。
  •     對每個event_id采用最新出現(xiàn)的。
  •     按輸入索引排序。

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

相關(guān)文章

  • 淺談數(shù)據(jù)庫緩存最終一致性的四種方案

    淺談數(shù)據(jù)庫緩存最終一致性的四種方案

    緩存是軟件開發(fā)中一個非常有用的概念,數(shù)據(jù)庫緩存更是在項目中必然會遇到的場景,緩存一致性的保證,更是在面試中被反復(fù)問到。下面我們就一起來了解一下
    2021-04-04
  • 幾種常用DB驅(qū)動和DB連接串小結(jié)

    幾種常用DB驅(qū)動和DB連接串小結(jié)

    幾種常用DB驅(qū)動和DB連接串,方便大家用各種程序,連接數(shù)據(jù)庫。
    2009-12-12
  • mssql 區(qū)分大小寫的詳細說明

    mssql 區(qū)分大小寫的詳細說明

    mssql區(qū)分大小寫,沒想到mysql也區(qū)分大小寫。相關(guān)的文章稍后奉獻給大家
    2008-03-03
  • 詳解關(guān)于Dbeaver的常用操作

    詳解關(guān)于Dbeaver的常用操作

    這篇文章主要介紹了詳解關(guān)于Dbeaver的常用操作,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2020-11-11
  • Hadoop 2.x偽分布式環(huán)境搭建詳細步驟

    Hadoop 2.x偽分布式環(huán)境搭建詳細步驟

    這篇文章主要為大家詳細介紹了Hadoop 2.x偽分布式環(huán)境搭建詳細步驟,感興趣的朋友可以參考一下
    2016-05-05
  • SQL修改語法語句梳理總結(jié)

    SQL修改語法語句梳理總結(jié)

    這篇文章主要介紹了關(guān)于SQL修改語句的語法梳理總結(jié),文中附含詳細的修改語句示例,有需要的朋友 可以借鑒參考下,希望能夠有所幫助
    2021-10-10
  • Dbeaver做數(shù)據(jù)遷移的詳細過程記錄

    Dbeaver做數(shù)據(jù)遷移的詳細過程記錄

    DBeaver是一款跨平臺的通用數(shù)據(jù)庫開源管理工具,支持 MySQL,PostgreSQL,Oracle,DB2,MSSQL,Sybase,Mimer,HSQLDB,Derby以及其他兼容JDBC的數(shù)據(jù)庫,下面這篇文章主要給大家介紹了關(guān)于Dbeaver做數(shù)據(jù)遷移的詳細過程,需要的朋友可以參考下
    2023-05-05
  • 數(shù)據(jù)設(shè)計之權(quán)限的實現(xiàn)

    數(shù)據(jù)設(shè)計之權(quán)限的實現(xiàn)

    這篇文章主要介紹了數(shù)據(jù)設(shè)計之權(quán)限的實現(xiàn),文章圍繞主題展開詳細的內(nèi)容介紹,具有一定的參考價值,需要的朋友可以參考一下
    2022-08-08
  • Access轉(zhuǎn)成SQL數(shù)據(jù)庫的方法

    Access轉(zhuǎn)成SQL數(shù)據(jù)庫的方法

    很多朋友想用SQL2000數(shù)據(jù)庫的編程方法,但是卻又苦于自己是學ACCESS的,對SQL只是一點點的了解而已,這里我給大家提供以下參考---將ACCESS轉(zhuǎn)化成SQL2000的方法和注意事項。
    2015-09-09
  • dbeaver導(dǎo)入導(dǎo)出數(shù)據(jù)庫簡單圖文教程

    dbeaver導(dǎo)入導(dǎo)出數(shù)據(jù)庫簡單圖文教程

    DBeaver是一款適用于開發(fā)人員、數(shù)據(jù)庫管理員和分析師的多平臺、多數(shù)據(jù)庫管理工具,這篇文章主要給大家介紹了關(guān)于dbeaver導(dǎo)入導(dǎo)出數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下
    2024-01-01

最新評論