正确使用PostgreSQL的数组类型

在Heap中，我们依靠PostgreSQL支撑大多数后端繁重的任务，我们存储每个事件为一个hstore blob，我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组，并将这些事件按时间排序。 Hstore能够让我们以灵活的方式附加属性到事件中，而且事件数组赋予了我们强大的性能，特别是对于漏斗查询，在这些查询中我们计算不同转化渠道步骤间的输出。

在这篇文章中，我们看看那些意外接受大量输入的PostgreSQL函数，然后以高效，惯用的方式重写它。 

你的***反应可能是将PostgreSQL中的数组看做像C语言中对等的类似物。你之前可能用过变换阵列位置或切片来操纵数据。不过要小心，在PostgreSQL中不要有这样的想法，特别是数组类型是变长的时，比如JSON、文本或是hstore。如果你通过位置来访问PostgreSQL数组，你会进入一个意想不到的性能暴跌的境地。

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

在这边究竟发生了什么呢? 关键在于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(N²), 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;