PostgreSQL中如何使用数组

139次阅读

共计 4711 个字符，预计需要花费 12 分钟才能阅读完成。

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