LIKE查询的最佳Postgres文本索引？

Question

使用Postgres 9.5，我有一个名为addresses的表。

CREATE TABLE addresses (
    id        integer PRIMARY KEY,
    address   text
);

这个表格中有750万行数据，例如：

1, "1600 Pennsylvania Avenue NW, Washington, DC, 20500"

我在我的应用程序中使用这个表格进行自动建议搜索，因此需要使用这种类型的查询：

SELECT * FROM addresses WHERE address LIKE '123 Main St%';

我创建了这个索引：

CREATE INDEX address_idx ON addresses (address);

但问题在于它需要大约1秒钟的时间，这太慢了。

以下是查询计划：

EXPLAIN SELECT * FROM addresses WHERE address LIKE '123 Main St%';
----
Seq Scan on addresses  (cost=0.00..161309.76 rows=740 width=41)
  Filter: (address ~~ '123 Main St%'::text)

我尝试创建了几种gin索引，但它们要么没有效果，要么使查询变慢。虽然我不确定我是否使用正确。

有什么想法可以创建一个针对这种查询进行优化的索引吗？

---

编辑

到目前为止找到的最佳解决方案是使用文本范围扫描：

SELECT *
FROM addresses
WHERE address >= '123 Main St' AND
      address <= concat('123 Main St', 'z');

- Tyler

---

这将始终是前缀搜索吗？那么您可以尝试 WHERE address BETWEEN '123 Main St' AND '123 Main Su'。这应该会在索引上产生范围扫描。 - Thilo

该查询应该可以正常工作。也许是文本类型不兼容导致无法使用索引。 - Gordon Linoff

@Thilo 谢谢！使用BETWEEN产生了与LIKE查询相同的结果，并将时间缩短到13毫秒。回答你的问题，是的，这将始终是一个前缀搜索。我不喜欢这种方法的唯一一件事是我必须想出字母表中的下一个字母或数字，而不是使用通配符。有没有其他方法可以生成范围扫描而不必编写那种逻辑？ - Tyler

理想情况下，查询规划器应该检测到前缀搜索并将其转换为BETWEEN查询。即使没有检测到，最坏的情况也应该是索引扫描（而不是表扫描）-- 尽管在这种情况下这并不会更快。 - Thilo

Answer 1

这是关于`between`方法的详细说明，内容过长不适合作为评论。如果你在使用标准ASCII字符，可以使用波浪线技巧：

SELECT *
FROM addresses
WHERE address >= '123 Main St' AND
      address <= concat('123 Main St', '~');

波浪号的ASCII值比其他字符大。

我注意到Postgres也应该为LIKE查询使用索引。我的猜测是问题与类型的兼容性有关。也许如果您将模式转换为varchar()，Postgres会使用索引。

- Gordon Linoff

---

谢谢回复。确实很聪明，但我在查询中遇到了麻烦：ERROR: argument of AND must be type boolean, not type text - on line 4。顺便说一下，我正在使用标准ASCII字符。您能详细说明一下将模式转换为varchar()是什么意思吗？您是指列吗？ - Tyler

@Tyler...这可能是由于运算符优先级引起的。 - Gordon Linoff

移除双竖线并添加波浪符号，即“123 Main St”，可以解决错误但不返回任何结果。我还尝试了“123 Main S”，但没有成功。参考ASCII表，似乎“z”是最大的ASCII值字符，我可以使用它来返回任何结果。编辑：concat('123 Main St'，'z')有效。 - Tyler

@Tyler...有趣。波浪号在其他情况下对我有效。至少z有效。 - Gordon Linoff

谢谢你的帮助。我认为使用你的连接建议进行范围扫描是最好的解决方案！ - Tyler

Answer 2

你可以尝试以下三件事情：

1. 如果你的数据库是 'C' 地域（你可以通过在 psql 提示符下键入 \l 来检查），则常规的 Btree 索引应该有助于优化类似于 LIKE 'abc%' 的查询。
2. 如果不是，你可以尝试在创建 Btree 索引时使用适当的操作符类。例如：CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);
3. 如果这不能解决问题，你也可以尝试使用 GiST / GIN。更详细的信息可以在这里查看：链接。

如果您想了解更多信息，可以阅读 Erwin 在 StackOverflow 上的回答，其中详细说明了不同的 Postgres 索引如何与 LIKE / ILIKE 一起工作。您可以在此处查看：链接。

- Robins Tharakan