我有一个表,其中一列包含 url 的子字符串。它总是没有方案的 url 的最左边部分。
ID | 领域 | 专栏 1 | 专栏2 |
---|---|---|---|
1个 | 例子.com | 价值1 | 值2 |
2个 | 例子.org | 价值3 | 值4 |
3个 | example.net/zh-CN | 值5 | 价值6 |
4个 | example.net/zh-CN | 值7 | 价值8 |
域中永远不会重叠,因此不会有 domain 的行example.net
,也不会有空的域。
我想搜索并找到给定 url 的匹配行,例如example.org/sub/sub/sub/test.html
它应该返回 id 为 2 的行。
到目前为止,我已经使用SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%')
which 给出了我想要的,但它总是对表进行 seq 扫描,即使我在该domain
列上有一个索引。我希望 Postgres 可以得出一些结论并使用索引。
我怎样才能提高这个性能?
似乎您输入的所有可能子字符串都用字符分隔
/
。为了说明我的观点:基于这个假设,这里有一个高效的解决方案来克服Daniel解释的主要障碍:
称呼:
小提琴
该函数在第一次命中后退出。如果未找到任何内容,您将不会获得任何行。
“魔法”是在过滤器表达式中切换操作数,以便输入可以是模式并且查询再次“可搜索”。
此外,在将输入拆分为其原子部分之后,我们可以匹配 with
=
instead ofLIKE
。现在,运算符的左右不再重要,因为=
有一个COMMUTATOR
(本身) - 而LIKE
不是没有。有关的:如果输入真的可以在任何位置截断,请将循环的开头替换为:
只是效率稍差。对输入中的每个字符进行一次索引查找。但是你可能至少会把领先的域名作为模式......
您所需要的只是一个普通的 B 树索引
(domain)
。UNIQUE
根据您告诉我们的情况,提供所述索引的列上可能已经存在约束或索引。string_to_table()
需要 Postgres 14 或更高版本。早期版本提供了多种选择。该解决方案不依赖于此实现细节。可以使用 btree 索引的前缀匹配 LIKE 查询具有以下形式:
where
parameter
是一个常量并且本身不包含通配符 (_
和%
)在这个问题的情况下,情况恰恰相反:
parameter LIKE column||'%'
。一方面,Postgres 无法预先知道某些行是否column
可能包含_
或字符。%
所以它需要阅读所有这些。就算有办法用btree索引来加速匹配,这里也用不上。有一个前缀扩展提供了一种专门优化的数据类型,可以在 GIST 索引的帮助下找到最长的前缀匹配。这似乎可以很好地加速您正在进行的搜索。