知识卡片

普通读书笔记卡

Range类型:把"两列区间查询"变成一个原生可索引的类型

理解等级:未标注

表示一个区间(比如IP地址段、时间段),最直观的关系型建模方式是用两个 独立的列(起始值、结束值),查询”某个值落在哪个区间”时写 start <= x AND end >= x。这种建模方式有一个容易被忽视的索引效率 问题:即使分别给起止两列建了索引,优化器也只能各自扫描出满足单边 条件的一大批候选行、再用位图AND把两边结果合并,其中一边的索引扫描 往往会带回大量后续被过滤掉的无关行(真实案例中,对31万多行数据的 区间表这样查询,仅一侧索引扫描就会命中31万+候选行,最终却只留下1行 真正满足条件,其余全部在合并后被Filter条件丢弃),整个查询代价 高达cost=2761.49..9277.91、实际执行时间113毫秒。PostgreSQL的Range 类型提供了另一种建模思路:把”区间”本身当作一个原生的数据类型(而不是 拆成两列),配合GiST索引和”包含”操作符@>,让索引直接支持”这个区间 是否包含某个值”这类空间/区间语义的查询。同样的IP地址范围数据改用 inetrange类型建模、建GiST索引后,查询ip_range @> '目标IP'的代价 骤降到cost=0.29..8.30、执行时间40毫秒——不是简单的性能调优,而是把 问题从”两个独立条件的AND合并”重新定义成”一个类型上的原生区间包含 判断”,索引结构本身就直接匹配问题的语义,不再需要事后过滤掉大量 “看起来满足单边条件、实际不满足整体条件”的行。这是”选对数据类型/ 建模方式本身就能决定索引能不能真正命中问题”的一个具体例子:不是所有 查询效率问题都能靠加索引解决,有时候问题出在用错了数据模型。

这张卡片看不懂?

已有反馈 · 0 / 10 条

    还没有人提交反馈。

    原书出处

    参考来源

    - 位置:《PostgreSQL修炼之道:从小工到专家(第2版)》第5章"数据类型" 5.14.1节"Range类型简介"(源文件: _epub-src/OEBPS/Text/chapter142.xhtml) - 结论依据:原文明确用IP地址库的实测EXPLAIN ANALYZE对比两种建模方式: 用`ip_begin`/`ip_end`两列+两个索引查询时"Rows Removed by Filter: 317435"、总代价"cost=2761.49..9277.91",改用`inetrange`类型+GiST 索引后代价降为"cost=0.29..8.30",直接给出Range类型相比双列建模的 索引效率差异及具体数据支撑。 - 原始内容:从上面的执行计划来看,对索引进行范围扫描效率仍不太高, 那么有没有更高效的查询方法呢?答案是肯定的,这就是使用Range类型, 通过创建空间索引的方式来执行……从中可以看到查询性能得到了很大的 提高。