正确使用PostgreSQL的数组类型

正确使用PostgreSQL的数组类型

在Heap中，我们依靠PostgreSQL支撑大多数后端繁重的任务，我们存储每个事件为一个hstore blob，我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组，并将这些事件按时间排序。

AD：

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

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

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

这种情况几星期前在Heap出现了。我们在Heap为每个跟踪用户维护一个事件数组，在这个数组中我们用一个hstoredatum代表每个事件。我们有一个导入管道来追加新事件到对应的数组。为了使这一导入管道是幂等的，我高山茶 https://www.360docs.net/doc/141063576.html,们给每个事件设定一个event_id，我们通过一个功能函数重复运行我们的事件数组。如果我们要更新附加到事件的属性的话，我们只需使用相同的event_id转储一个新的事件到管道中。

所以，我们需要一个功能函数来处理hstores数组，并且，如果两个事件具有相同的event_id时应该使用数组中最近出现的那个。刚开始尝试这个函数是这样写的：

--Thisisslow,andyoudon'twanttouseit!----Filteranarrayofeventssuchthatthereisonlyoneeventwitheachev ent_id.--Whenmorethanoneeventwiththesameevent_idispresent,takethelatestone.CREATEORREPLA CEFUNCTIONdedupe_events_1(eventsHSTORE[])RETURNSHSTORE[]AS$$SELECTarray_agg(e vent)FROM(--Filterforrank=1,i.e.selectthelatesteventforanycollisionsonevent_id.SELECTeventFROM (--Rankelementswiththesameevent_idbypositioninthearray,descending.SELECTevents[sub]ASevent,s ub,rank()OVER(PARTITIONBY(events[sub]-

'event_id')::BIGINTORDERBYsubDESC)FROMgenerate_subscripts(events,1)ASsub)deduped_event sWHERErank=1ORDERBYsubASC)to_agg;$$LANGUAGESQLIMMUTABLE;

这样奏效，但大输入是性能下降了。这是二次的，在输入数组有100K各元素时它需要大约40秒！

这个查询在拥有 2.4GHz的i7CPU及16GB Ram的macbook pro上测得，运行脚本为：https:https://www.360docs.net/doc/141063576.html,drob9180760。

在这边究竟发生了什么呢? 关键在于PostgreSQL存贮水草玛瑙 https://www.360docs.net/doc/141063576.html,了一个系列的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，它解析数组并返回一组条目。这样一来，我们就不需要在数组中显式加入索引了。

--Filteranarrayofeventssuchthatthereisonlyoneeventwitheachevent_id.--Whenmorethanoneeventwithth esameevent_id,ispresent,takethelatestone.CREATEORREPLACEFUNCTIONdedupe_events_2(event sHSTORE[])RETURNSHSTORE[]AS$$SELECTarray_agg(event)FROM(--Filterforrank=1,i.e.selectt helatesteventforanycollisionsonevent_id.SELECTeventFROM(--Rankelementswiththesameevent_idby positioninthearray,descending.SELECTevent,row_numberASindex,rank()OVER(PARTITIONBY(eve nt-

'event_id')::BIGINTORDERBYrow_numberDESC)FROM(--Useunnestinsteadofgenerate_subscriptst oturnanarrayintoaset.SELECTevent,row_number()OVER(ORDERBYevent-

'time')FROMunnest(events)ASevent)unnested_data)deduped_eventsWHERErank=1ORDERBYindex ASC)to_agg;$$LANGUAGESQLIMMUTABLE;

结果是有效的，它花费的时间跟输入数组的大小呈线性关系。对于100K个元素的输入它需要大约半秒，而之前的实现需要40秒。

这实现了我们的需求：

一次解析数组，不需要unnest。

按event_id划分。

对每个event_id采用最新出现的。

按输入索引排序。

教训：如果你需要访问PostgreSQL数组的特定位置，考虑使用unnest代替。

我们希望能够避免失误。有任何意见或其他PostgreSQL的秘诀请@heap。

[1]特别说明一下，我们使用一个名为Citus Data的贴心工具。更多内容在另一篇博客中！

[2]参考：https:https://www.360docs.net/doc/141063576.html,featuresfunnels。特别说明一下，计算转换程序需要对用户已完成事件的数组进行一次扫描，但不需要任何join。

原文链接：dont-iterate-over-a-postgres-array-with-a-loop

译文链接：translatedont-iterate-over-a-postgres-array-with-a-loop【编辑推荐】有趣的JavaScript 原生数组函数Python之父：为什么Python数组下标从0开始PostgreSQL的最佳特性你用了吗？【责任编辑：陈四芳TEL：（010）68476606】

Java程序设计专家门诊

精选目前国内外最流行的程序设计语言——Java作为本书的选题，并以丰富的内容来解决读者学习该语言时可能遇到的各种问题。以专业