在 Postgres 或 CouchDB 中进行全文搜索？

Question

我访问了 geonames.org 并导入了德国城市及其所有地区的所有数据。

如果我输入“汉堡”，它会列出“汉堡中心、汉堡机场”等。该应用程序位于封闭网络中，无法访问互联网，因此我无法访问 geonames.org Web 服务并且必须导入数据。 :( 这座城市及其所有地区都像一个汽车整体一样运转。因此，每次按键都会产生 XHR 请求，依此类推。

现在我的客户问是否有可能将世界上所有的数据都存入其中。最后，大约 5.000.000 行和 45.000.000 个备用名称等。Postgres

每个查询需要大约 3 秒，这使得自动完成功能无法使用。

现在我想到了CouchDb，已经使用过它了。我的问题：

我想发布“Ham”，并且希望 CouchDB 获取以“Ham”开头的所有文档。如果我输入“汉堡”，我希望它返回汉堡等。

CouchDB 是合适的数据库吗？您可以推荐哪些其他数据库能够以低延迟（可能在内存中）和数百万个数据集进行响应？数据集不会定期更改，它相当静态！

Answer 1

如果我理解你的问题正确的话，可能你所需要的一切都已经内置在 CouchDB 中。

1. 获取一系列名称以“Ham”开头的文档。您可以使用带有字符串范围的请求： startkey="Ham"&endkey="Ham\ufff0"
2. 如果您需要更全面的搜索，您可以创建一个包含其他地点名称作为键的视图。因此，您可以再次使用上述技术查询范围。

这是一个实现此目的的视图函数：

function(doc) {
    for (var name in doc.places) {
        emit(name, doc._id);
    }
}

另请参阅有关 CouchDB typeahead 和自动完成搜索 以及邮件列表上有关 CouchDB 自动完成。

Answer 2

使用 PostgreSQL 优化搜索

您的搜索锚定在开头并且不需要模糊搜索逻辑。这不是全文搜索的典型用例。

如果它变得更加模糊或者您的搜索没有从一开始就锚定，请在此处查找更多信息：

• 自动完成字段的类似 UTF-8 字符串
• 模式匹配LIKE、SIMILAR TO 或正则表达式

在 PostgreSQL 中，您可以利用高级索引功能，这将使查询非常快。特别是，请查看 运算符类 和 表达式索引。

1. text_pattern_ops

假设您的列是文本类型，您将为文本模式运算符使用特殊索引，如下所示：

CREATE INDEX name_text_pattern_ops_idx
ON tbl (name text_pattern_ops);

SELECT name
FROM   tbl
WHERE  name ~~ ('Hambu' || '%');

这是假设您使用的数据库区域设置不是C - 在您的情况下很可能是 de_DE.UTF-8 。您还可以使用语言环境“C”设置数据库。我在这里引用手册：

如果您确实使用 C 语言环境，则不需要 xxx_pattern_ops
运算符类，因为具有默认运算符类的索引是
可用于 C 语言环境中的模式匹配查询。

更新：在现代 Postgres 中，考虑COLLATE "C"。请参阅：

• text_pattern_ops 和 COLLATE "C" 之间有区别吗？

表达式索引

2.我想你的 还希望搜索不区分大小写。因此，让我们采取另一个步骤，使其成为表达式的索引：

CREATE INDEX lower_name_text_pattern_ops_idx
ON tbl (lower(name) text_pattern_ops);

SELECT name
FROM   tbl
WHERE  lower(name) ~~ (lower('Hambu') || '%');

要使用索引，WHERE 子句必须与索引表达式匹配。

3. 优化索引大小和速度

最后，您可能还想对前导字符的数量施加限制，以最小化索引的大小并进一步加快速度：

CREATE INDEX lower_left_name_text_pattern_ops_idx
ON tbl (lower(left(name,10)) text_pattern_ops);

SELECT name
FROM   tbl
WHERE  lower(left(name,10)) ~~ (lower('Hambu') || '%');

left() 是在 Postgres 9.1 中引入的。使用 substring(name, 1,10) 在旧版本中。

4.覆盖所有可能的请求

超过10个字符的字符串怎么办？

SELECT name
FROM   tbl
WHERE  lower(left(name,10)) ~~ (lower(left('Hambu678910',10)) || '%');
AND    lower(name) ~~ (lower('Hambu678910') || '%');

这看起来多余，但是您需要以这种方式拼写出来才能实际使用索引。索引搜索会将其范围缩小到几个条目，附加子句会过滤其余条目。尝试找到最佳点。取决于数据分布和典型用例。 10 个字符似乎是一个很好的起点。对于超过 10 个字符，left() 有效地变成了一种非常快速且简单的哈希算法，对于许多（但不是全部）用例来说已经足够好了。

更新：在现代 Postgres 中，请考虑使用 ^@ 运算符。请参阅：

• PostgreSQL LIKE 查询性能变化

5. 使用<优化磁盘表示code>CLUSTER

因此，主要的访问模式将是根据我们的索引 lower_left_name_text_pattern_ops_idx 检索一堆相邻行。而你主要阅读，几乎不写作。这是CLUSTER的教科书案例。 手册：

当表被集群时，它会根据索引信息进行物理重新排序。

对于像您这样的巨大表，这可以显着提高响应时间，因为要获取的所有行都位于磁盘上的相同或相邻块中。

第一次调用：

CLUSTER tbl USING lower_left_name_text_pattern_ops_idx;

将保存要使用哪个索引的信息，连续的调用将对表重新集群：

CLUSTER tbl;
CLUSTER;    -- cluster all tables in the db that have previously been clustered.

如果您不想重复：

ALTER TABLE tbl SET WITHOUT CLUSTER;

但是，CLUSTER 在表上采用独占锁。如果这是一个问题，请查看 pg_repack 或 pg_squeeze，无需对表进行独占锁即可执行相同操作。

6. 防止结果中出现太多行

要求搜索字符串至少包含 3 或 4 个字符。我添加这个是为了完整性，你可能还是这样做。
LIMIT 返回的行数：

SELECT name
FROM   tbl
WHERE  lower(left(name,10)) ~~ (lower('Hambu') || '%')
LIMIT  501;

如果您的查询返回超过 500 行，请告诉用户缩小搜索范围。

7. 优化过滤方法（运算符）

如果您绝对必须挤出最后一微秒，则可以使用 text_pattern_ops 系列。就像这样：

SELECT name
FROM   tbl
WHERE  lower(left(name, 10)) ~>=~ lower('Hambu')
AND    lower(left(name, 10)) ~<=~ (lower('Hambu') || chr(2097151));

最后这个特技你收获甚微。通常，标准运算符是更好的选择。

---

如果您做到了这一切，搜索时间将减少到几毫秒。

Answer 3

我认为更好的方法是将数据保存在数据库（Postgres 或 CouchDB）上，并使用全文搜索引擎对其进行索引，例如 Lucene、Solr 或 ElasticSearch.

话虽如此，有一个将 CouchDB 与 Lucene 集成的项目。