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

问题描述：

使用Postgres 9.5，我有一个表addresses。LIKE查询的最佳Postgres文本索引？

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');

这将永远是一个前缀搜索？然后你可以尝试'123 Main St'和'123 Main Su'之间的地址。这应该会在索引上产生范围扫描。 – Thilo

索引应该适用于此查询。也许与某些文本类型不兼容会阻止使用索引。 –

@Thilo谢谢！使用BETWEEN产生与LIKE查询相同的结果，并将时间缩短到13ms。要回答你的问题，是的，这将永远是一个前缀搜索。我不喜欢这种方法的唯一情况是我必须拿出下一个字母或下一个数字，而不是使用通配符。有没有其他的方式来产生一个范围扫描，而不必写这种逻辑？ – Tyler

答

这是对between方法的制定和过长的评论。

如果使用标准的ASCII字符，你可以用波浪线技巧：

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

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

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

感谢您的回复。确实很聪明，但我遇到了这个查询的麻烦：'错误：AND的参数必须是布尔类型，而不是输入文本 - 在第4行。我使用标准的ASCII字符btw。你能否通过将模式转换为varchar（）来详细说明你的意思？你的意思是专栏？ – Tyler

@泰勒。。。这可能是由于运营商的优先权。 –

删除双重管道并添加代字号（即'123 Main St〜'）可解决该错误，但不会返回任何结果。我也尝试过'123主S〜'，但没有奏效。引用ASCII表格，似乎'z'是我可以用来返回任何结果的最大的ASCII值字符。编辑：concat（'123主街'，'z'）的作品。 – Tyler

答

三样东西，你可以尝试：

如果你的数据库是“C”区域（您可以用\l检查的psql提示），然后定期Btree指标应在优化LIKE 'abc%'类型的帮助查询。
如果不是，则可以在创建Btree索引时尝试使用合适的操作符类。对于例如CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);
如果这不起作用，您也可以尝试使用GiST/GIN，更详细的给出here。

如果你想知道更多，你应该阅读欧文的StackOverflow的答案here，即不同的细节Postgres的指标如何与LIKE/ILIKE工作。

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

相关推荐