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

yxyvkwin 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(109)

我有一个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字符的字段或索引的整个字段是一样有效。

2条答案

实际上，如果安装不在区域"C"上运行，普通的B树索引对于LIKE（~~）或regexp（~）的模式匹配是无用的，即使是左锚定模式，如果您的安装没有在locale "C"上运行-这是典型的情况。以下是模式匹配和索引的概述：

使用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 varchar_pattern_ops);

字符串
您的原始查询可以使用此索引：

... WHERE bus_phone LIKE '555%'

型
functional index on the first 3 characters as described in the answer by @a_horse的性能在本例中几乎相同。
fiddle的
老麻雀
通常，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.
但是，列中的字符越多，函数索引的好处就越大。
如果字符串太长，你甚至可能不得不使用前缀索引（或其他类型的散列）。索引有一个最大长度。请参阅：

Index max row size error
如果 * 您决定使用函数索引，请考虑使用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);

型

对于函数索引，您必须使用标准的SQL强制转换语法。这很好用--很像left()，但像@a_horse一样，我更喜欢left()。

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

型

当使用like '555%'时，也会使用完整列的索引。不需要只索引前三个字符。
如果您确实希望仅索引前3个字符（例如为了保存空间），那么你可以使用left()函数：

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

字符串
但是为了使用该索引，您需要在where子句中使用该表达式：

where left(bus_phone,3) = '555';

型
但再次强调：这很可能是矫枉过正，完整列上的索引将足够好，可以用于其他查询，例如。bus_phone = '555-1234'，而前三个字符的索引不会。