在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秒!
这个查询在拥有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代替。
PostgreSQL
更新动态
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]