在Heap中，我们依赖PostgreSQL完成大部分后端繁重工作，我们将每个事件存储为hstoreblob，并为每个跟踪的用户维护一个PostgreSQL数组，其中包含已完成的事件，并对这些事件进行排序按时间。Hstores允许我们以灵活的方式将属性附加到事件，事件数组为我们提供了出色的性能，特别是对于我们计算不同转换漏斗步骤之间的输出的漏斗查询。在这篇文章中，我们将研究那些意外接受大量输入的PostgreSQL函数，然后以高效、惯用的方式重写它。您的第一反应可能是将PostgreSQL中的数组视为它们的C等价物。您之前可能通过转换数组位置或切片来操作过数据。但是注意在PostgreSQL中不要这样想，尤其是数组类型是可变长度的时候，比如JSON，text或者hstore。如果您按位置访问PostgreSQL数组，您可能会遇到意想不到的性能损失。几周前，这发生在Heap身上。我们在Heap中为每个被跟踪的用户维护一个事件数组，在这个数组中我们使用一个hstoredatum来表示每个事件。我们有一个导入管道来将新事件附加到相应的数组。为了使这个导入管道幂等，我们为每个事件分配一个event_id，并通过实用函数重复运行我们的事件数组。如果我们想更新附加到事件的属性，我们只需将一个新事件转储到具有相同event_id的管道中。因此，我们需要一个处理hstores数组的实用程序，如果两个事件具有相同的event_id，它应该使用数组中最近的一个。起初尝试这个函数是这样写的：--Thisisslow,andyoudon'twanttouseit!--Filteranarrayofeventssuchthatthereisonlyoneeventwiththeachevent_id。Filterforrank=1，即选择最新的事件foranycollisionsonevent_id.SELECTeventFROM(--Rankelementswiththesameevent_idbypositioninthearray,descending.SELECTevents[sub]ASevent,sub,rank()OVER(PARTITIONBY(events[sub]->'scriptevent_id')::BIGINTORDERBYsubysubyerDESC)ASsub)deduped_rankBYCHERDERWto_agg;$$LANGUAGESQLIMMUTABLE;这行得通，但是对于大输入，性能会下降。这是二次方，当输入数组有100K个元素时大约需要40秒！此查询是在配备2.4GHzi7CPU和16GBRam的macbookpro上测得的。运行脚本为：https://gist.github.com/drob/9180760。这里发生了什么？关键是PostgreSQL将一系列hstores存储为数组值，而不是指向值的指针。三个hstore的数组看起来像{"event_id=>1,data=>foo","event_id=>2,data=>bar","event_id=>3,data=>baz"}而不是{[pointer],[pointer],[pointer]}对于那些具有不同长度变量的，例如。hstores、jsonblob、varchars或文本字段，PostgreSQL必须找到每个变量的长度。对于evaluatevents[2]，PostgreSQL解析events从左边读到的，直到第二次第一次读到的数据。然后forevents[3]，她又从第一个index开始扫描，直到读到第三个数据！因此，对于数组中的每个索引，evaluatedevents[sub]是O(sub)，evaluationvents[sub]是O(N2)，其中N是数组的长度。PostgreSQL可以获得更好的解析结果，在这种情况下它可以对数组进行一次解析。真正的答案是肯定的可变长度元素是用指向数组值的指针实现的，因此我们总是可以在常数时间内处理evaluatevents[i]。即便如此，我们也不应该让PostgreSQL处理它，因为这不是惯用的Inquire。我们可以使用unnest代替generate_subscripts，它解析一个数组并返回一组条目。这样，我们就不需要显式地向数组添加索引。--Filteranarrayofeventssuchthatthereisonlyoneeventwitheachevent_id.--Whenmorethanoneeventwiththesameevent_id,ispresent,takethelatestone.CREATEORREPLACEFUNCTIONdedupe_events_2(eventsHSTORE[])RETURNSHSTORE[]AS$$SELECTarray_agg(event)FROM(--Filterforrank=1,i.e.selectthelatesteventforanycollisionsonevent_id.SELECTeventFROM(--Rankelementswiththesameevent_idbypositioninthearray,descending.SELECTevent,row_numberASindex,rank()OVER(PARTITIONBY(event->'event_id')::BIGINTORDERBYrow_numberDESC)FROM(--Useunnestinsteadofgenerate_subscriptstoturnanarrayintoaset.SELECTevent,row_number()OVER(ORDERBYevent->'time')FROMunnest(REASevents)ASORandeYex1HERanendups=unnested_eventsWed;$)to_agg$LANGUAGESQLIMMUTABLE;结果是有效的，它所花费的时间与输入数组的大小成线性关系。对于100K个元素的输入，它大约需要半秒，而之前的实现需要40秒。这满足了我们的需求：解析数组一次，不需要unnest。除以event_id。使用***o每个event_id的出现次数。按输入索引排序。经验教训：如果您需要访问PostgreSQL数组中的特定位置，请考虑改用unnest。我们想避免错误。任何评论或其他PostgreSQL提示请@heap。[1]特别是，我们使用了一个名为CitusData的好工具。更多在另一个博客！[2]参考资料：https://heapanalytics.com/features/funnels。特别是，计算转换例程需要扫描用户完成事件的数组，但不需要任何连接。原文链接：http://blog.heapanalytics.com/dont-iterate-over-a-postgres-array-with-a-loop/翻译链接：http://www.oschina.net/translate/dont-iterate-带循环的postgres数组
