postgresql - 在电话字段的前 3 个字符(区号)上创建索引？

Question

我有一个 Postgres 表，其中电话字段存储为 varchar(10)，但我们经常搜索区号，例如:

select * from bus_t where bus_phone like '555%'

我想创建一个索引来促进这些搜索，但在尝试时出现错误:

CREATE INDEX bus_ph_3 ON bus_t USING btree (bus_phone::varchar(3));

ERROR: 42601: syntax error at or near "::"

我的第一个问题是，如何实现这一点，但我也想知道对字段的前 X 个字符进行索引是否有意义，或者对整个字段进行索引是否同样有效。

Answer 1

实际上，普通 B 树索引通常无用用于与LIKE 的模式匹配(~~ ) 或正则表达式 (~)，即使使用左锚定模式，如果您的安装在 "C" 以外的任何其他区域设置上运行，即典型案例。这是一个 overview over pattern matching and indices in a related answer on dba.SE

使用 varchar_pattern_ops 运算符类(匹配您的 varchar 列)创建索引并确保 read the chapter on operator classes in the manual .

CREATE INDEX bus_ph_pattern_ops_idx ON bus_t (bus_phone <b>varchar_pattern_ops</b>);

您的原始查询可以使用此索引:

... WHERE bus_phone LIKE '555%'

性能functional index on the first 3 characters as described in the answer by @a_horse在这种情况下几乎相同。

-> SQLfiddle demo.

一般是functional index在相关的前导字符上 将是 是个好主意，但您的专栏只有 10 个字符。考虑 overhead per tuple is already 28 bytes .节省 7 个字节不足以产生很大的不同。添加函数调用的成本和 xxx_pattern_ops are generally a bit faster 的事实.在 Postgres 9.2 或更高版本中，完整列上的索引也可以用作 covering index in index-only scans.。

但是，列中的字符越多，函数索引的好处就越大。
You may even have to resort to a prefix index (or some other kind of hash) if the strings get too long. There is a maximum length for indices.

如果您决定使用函数式索引，请考虑使用 xxx_pattern_ops 变体以获得小的额外性能优势。请务必阅读利弊 in the manual在Peter Eisentraut's blog entry :

CREATE INDEX bus_ph_3 ON bus_t (left(bus_phone, 3) varchar_pattern_ops);

解释错误信息

你必须使用 standard SQL cast syntax用于功能指标。这会起作用 - 非常类似于带有 left() 的那个，但是像 @a_horse 我更喜欢 left()。

CREATE INDEX bus_ph_3 ON bus_t USING btree (cast(bus_phone AS varchar(3));