饿虎岗资源网

在PostgreSQL中使用数组时值得注意的一些地方
数据库 2024/11/15 佚名
3 1
饿虎岗资源网 Design By www.oxmxm.com

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


在这边究竟发生了什么呢"htmlcode"> 

{“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秒！

在PostgreSQL中使用数组时值得注意的一些地方

这个查询在拥有2.4GHz的i7CPU及16GB Ram的macbook pro上测得，运行脚本为：https://gist.github.com/drob/9180760。


在这边究竟发生了什么呢"htmlcode"> 

{“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代替。

饿虎岗资源网 Design By www.oxmxm.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
饿虎岗资源网 Design By www.oxmxm.com
www.oxmxm.com 饿虎岗资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
饿虎岗资源网 Design By www.oxmxm.com