在电话字段的前3个字符（区号）上创建索引？ [英] Create index on first 3 characters (area code) of phone field?

问题描述

我有一个Postgres表，手机字段存储为 varchar（10），但我们经常搜索区域代码，例如：

I have a Postgres table with a phone field stored as varchar(10), but we search on the area code frequently, e.g.:

select * from bus_t where bus_phone like '555%'

我想创建一个索引以方便这些搜索，但我尝试时遇到错误：

I wanted to create an index to facilitate with these searches, but I got an error when trying:

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

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

我的第一个问题是，我如何做到这一点，但我想知道是否有意义的索引字段的前X个字符或如果整个字段的索引是同样有效的。

My first question is, how do I accomplish this, but also I am wondering if it makes sense to index on the first X characters of a field or if indexing on the entire field is just as effective.

推荐答案

实际上，纯B树索引通常无用 c> LIKE （ ~~ ）或regexp（〜锚定模式，如果，您的安装在除C之外的任何其他语言环境运行，这是典型的情况。以下是概述dba.SE的相关答案中的过度模式匹配和索引

Actually, a plain B-tree index is normally useless for pattern matching with LIKE (~~) or regexp (~), even with left-anchored patterns, if your installation runs on any other locale than "C", which is the typical case. Here is an overview over pattern matching and indices in a related answer on dba.SE

使用 varchar_pattern_ops 运算符类（匹配 varchar 列），并确保阅读手册中有关操作符类的一章。

Create an index with the varchar_pattern_ops operator class (matching your varchar column) and be sure to read the chapter on operator classes in the manual.

CREATE INDEX bus_ph_pattern_ops_idx ON bus_t (bus_phone varchar_pattern_ops);

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

Your original query can use this index:

... WHERE bus_phone LIKE '555%'

在前3个字符上的功能索引的性能，如@a_horse的答案中所述< a>

Performance of a functional index on the first 3 characters as described in the answer by @a_horse is pretty much the same in this case.

- > SQLfiddle演示。

一般来说，功能索引对相关前导字符是一个好主意，但您的列只有10个字符。请考虑每个元组的开销已经是28个字节。保存7个字节只是不够大，不能产生很大的区别。添加函数调用的成本，以及 xxx_pattern_ops 通常会快一点。
在Postgres 9.2或更高版本中，完整列中的索引也可以用作索引索引 -

Generally a functional index on relevant leading characters would be be a good idea, but your column has only 10 characters. Consider that the overhead per tuple is already 28 bytes. Saving 7 bytes is just not substantial enough to make a big difference. Add the cost for the function call and the fact that xxx_pattern_ops are generally a bit faster. In Postgres 9.2 or later the index on the full column can also serve as covering index in index-only scans.

但是，列中的字符越多，功能索引的好处就越大。

您甚至可以 使用前缀索引或其他种类的哈希）如果字符串太长。

However, the more characters in the columns, the bigger the benefit from a functional index.
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 变体，以获得小的额外性能优势。请务必阅读手册中的 的优缺点。在 Peter Eisentraut的博客条目 ：

If you decide to go with the functional index, consider using the xxx_pattern_ops variant for a small additional performance benefit. Be sure to read about the pros and cons in the manual and in 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（）

Explain error message

You'd have to use the standard SQL cast syntax for functional indices. This would work - pretty much like the one with left(), but like @a_horse I'd prefer left().

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

这篇关于在电话字段的前3个字符（区号）上创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！