在PostgreSQL中使用数组时值得注意的一些地方

作者:无名 - 数据库其它 -

在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。


在这边究竟发生了什么呢? 关键在于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, 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秒!

2015421145745565.png (668×436)

这个查询在拥有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, 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代替。

IT人知识库 原文地址:http://www.itpeo.net/12848/441918.html





在PostgreSQL中使用日期类型时一些需要注意的地方

 当我们这些使用Rails的人看到例如5.weeks.from_nowor3.days.ago + 2.hou... ...

rfedfre

一个提升PostgreSQL性能的小技巧

 在一个(差)的PostgreSQL 查询中只要一个小小到改动(ANY(ARRAY[...])to ANY(V... ...

rfedfre

在PostgreSQL中实现递归查询的教程

 介绍 在Nilenso,哥在搞一个 (开源的哦!)用来设计和发起调查的应用。 下面这个是一个调查的例子: ... ...

设置CA证书来强化PostgreSQL的安全性的教程

在经历了多次的摸索实验后我终于成功地实现了SSL证书认证的功能,因此我想这次我要把这些步骤记录下来供日后查阅。 出于安全... ...

Instagram提升PostgreSQL性能的五个技巧

 随着Instagram的规模日益扩大,Postgres继续充当着Instagram的坚实基础,并存储着绝大部... ...

在PostgreSQL上安装并使用扩展模块的教程

安装模块 注意: 我的运行环境是 Ubuntu 10.04 和 PostgreSQL 8.4 首先安装 postgres... ...

介绍PostgreSQL中的范围类型特性

 PostgreSQL 9.2 的一项新特性就是范围类型 range types,通过这个名字你可以轻松猜出该... ...

在Windows下自动备份PostgreSQL的教程

背景 在我工作上一个使用PostgreSQL数据库的项目上需要一个自动化系统来每天执行备份。经过一番研究决定通过创建一个... ...

举例简单介绍PostgreSQL中的数组

 PostgreSQL 有很多丰富的开箱即用的数据类型,从标准的数字数据类型、到几何类型,甚至网络数据类型等等... ...

SQL之left join、right join、inner join的区别浅析

今天和某朋友聊天,谈到他们公司的一个小问题。如下: 表A设备表,存储MAC地址,省份,城市,区。 表B软件表,存储MAC... ...

在PostgreSQL的基础上创建一个MongoDB的副本的教程

我有一个偷懒的想法。这个好点子该如何开始呢?好吧,这是一个恰如其分的小疯狂:为什么不直接在Postgres的基础上建立我... ...

使用Bucardo5实现PostgreSQL的主数据库复制

下一代异步多个主数据库复制系统Bucardo 5发布了。这个版本删除了老版本中两个数据库源的限制,允许有更多的源数据库(... ...

详细讲解PostgreSQL中的全文搜索的用法

开发Web应用时,你经常要加上搜索功能。甚至还不知能要搜什么,就在草图上画了一个放大镜。 搜索是项非常重要的功能,所以像... ...

介绍PostgreSQL中的jsonb数据类型

PostgreSQL 9.4 正在加载一项新功能叫jsonb,是一种新型资料,可以储存支援GIN索引的JSON 资料。换... ...

rfedfre

介绍PostgreSQL中的Lateral类型

PostgreSQL 9.3 用了一种新的联合类型! Lateral联合的推出比较低调,但它实现了之前需要使用编写程序才... ...

mysql、mssql及oracle分页查询方法详解

本文实例讲述了mysql、mssql及oracle分页查询方法。分享给大家供大家参考。具体分析如下: 分页查询在web开... ...

telnet连接操作memcache服务器详解

memcache作为一款优秀的进程外缓存,常常被运用于高并发系统架构中。这里主要谈谈怎么通过telnet工具,查看mem... ...

SQL语句实现删除重复记录并只保留一条

代码如下: delete WeiBoTopics where Id in(select max(Id) from We... ...

50条SQL查询技巧、查询语句示例

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S... ...

SQL查询出表、存储过程、触发器的创建时间和最后修改时间示例

--查询建立时间 --表 select * from sysobjects where id=object_id... ...