大家可能还不知道 PostgreSQL 支持对表数据进行局部索引吧? 它的好处是既能加快这部分索引过的数据的读取速度, 又不会增加额外开销. 对于那些反复根据给定的 WHERE 子句读出来的数据, 最好的办法就是对这部分数据索引. 这对某些需要预先进行聚集计算的特定分析工作流来说, 很合适. 本帖中, 我将举一个例子说明如何通过部分索引优化数据查询.
假设有这样一个事件表, 结构如下:
每个事件关联一个用户, 有一个 ID, 一个时间戳, 和一个描述事件的 JSON. JSON 的内容包含页面的路径, 事件的类别 (如: 单击, 网页浏览, 表单提交), 以及其他跟事件相关的属性。
我们使用这个表存储各种事件日志. 假设我们手上有个事件自动跟踪器 , 能自动记录用户的每一个点击, 每一次页面浏览, 每一次表单提交, 以便我们以后做分析. 再假设我们想做个内部用的报表(internal dashboard)显示一些有价值的数据(high-value metrics), 如:每周的注册数量, 每天应收帐款. 那么, 问题就来了. 跟这个报表相关的事件, 只占该事件表数据的一小部分 -- 网站的点击量虽然很高, 但是只有很小一部分最终成交! 而这一小部分成交数据跟其他数据混杂放在一起, 也就是说, 它的信噪比很低.
我们现在想提高报表查询的速度. 先说注册事件吧, 我们把它定义为:注册页面(/signup/)的一次表单提交. 要获得九月份第一周的注册数量, 可以理解成:
对一个包含1千万条记录, 其中只有 3000 条是注册记录, 并且没有做过索引的数据集, 执行这样的查询需要 45 秒.
对单列做全索引(Full Indexes) : 大杂烩提高查询速度, 比较傻的办法是: 给事件相关的各种属性创建单列索引(single-column index):(data->>'type'),(data->>'path'), 和 time. 通过 bitmap, 我们可以把这三个索引扫描结果合并起来. 如果我们只是有选择地查询其中一部分数据, 而且相关索引依然存在内存中, 查询的速度会变得很快. 刚开始查询大概用 200 毫秒, 后面会降到 20 毫秒 — 比起要花 45 秒查询的顺序扫描, 确实有明显的提高.
这种索引方式有几个弊端:
数据写入的开销. 这种方式在每次 INSERT/UPDATE/DELETE 操作的时候, 需要修改这三个索引的数据. 导致像本例这样频需要繁写入数据的更新数据操作代价太高.
数据查询的限制. 这种方式同时也限制了我们自定义有价值(high-value)事件类型的能力. 比方说, 我们无法在 JSON 字段上做比范围查询更复杂的查询. 具体如:通过正则表达式搜索, 或者查找路径是/signup/ 开头的页面.
磁盘空间的使用. 本例中的提到的表占 6660 mb 磁盘空间, 三个索引和起来有 1026 mb, 随着时间的推移, 这些数字还会不断的暴涨.
局部索引（Partial Indexes）
我们分析用的注册事件，只占了表中全部数据的 0.03%。而全索引是对全部数据进行索引， 显然不合适。要提高查询速度， 最好的办法是用局部索引。
以我们对注册事件的定义为过滤条件，创建一个无关列(unrelated column)索引，通过该索引，PostgreSQL 很容易找到注册事件所在的行，查询速度自然要比在相关字段的3个全索引快的多。 尤其是对时间字段进行局部索引。具体用法如下:
CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'
这个索引的查询速度，会从刚开始的 200 毫秒, 降到 2 毫秒。只要多运行查询语句，速度自然就会加快。更重要的是，局部索引解决了前面提到的全索引的几个缺点。
索引只占 96 kb 磁盘空间, 是全索引的 1026 mb 的 1/10000。
只有新增的行符合注册事件的过滤条件， 才更新索引。由于符合条件的事件只有 0.03%，数据写入的性能得到很大的提高: 基本上，创建和更新这样的索引没有太大的开销。
这样的局部合并(partial join) 允许我们使用 PostgreSQL 提供的各种表达式作为过滤条件。索引中用到的 WHERE 子句，跟在查询语句中的用法没什么两样， 所以我们可以写出很复杂的过滤条件。 如：正则表达式， 函数返回结果，前面提到的前缀匹配。
不要索引结果是布尔值的断言
我见过有人直接索引布尔表达式：
(data->>'type') = 'submit' AND (data->>'path') = '/signup/'
，然后把时间字段放在第二项. 如：
CREATE INDEX event_signup_time ON event
(((data->>'type') = 'submit' AND (data->>'path') = '/signup/'), time)