sql - 在 Postgres 中查询时使用通配符或 "in list"

Question

我有几个表需要获取与 foo 相关的数据。表格的大小约为 10^8 行。

所以我需要从这些表中获取列包含子字符串“foo”的所有行。

select * from bar where my_col like '%foo%';

我知道这很慢所以我检查了可能的值:

select distinct my_col from bar where my_col like '%foo%';
-- => ('xx_foo', 'yy_foo', 'xx_foo_xx', 'foo' ... 'xx_foo_yy')

可能值的数量在 3 到 20 之间变化。

现在 '%foo%' 到底有多慢？

select * from bar where my_col like '%foo%';
-- or
select * from bar where my_col in('foo', 'xx_foo' ... 'foo_yy'); -- list_size = 20

关于何时使用什么的任何一般规则，或者测试不同情况的速度是唯一的方法吗？

编辑:我不拥有该表，并且列 foo 上不存在索引。所以无论如何都需要进行全表扫描。

Answer 1

如果你使用%foo%，你会得到一个全表扫描，这很慢。

如果您将 IN 与值列表一起使用，那么如果索引存在于您具有条件的列上，则可以使用该索引。

因此，如果可以，您应该避免使用 %foo%。根据新值在表中出现的频率，您可以考虑使用一个额外的表来保存不同的值并在查询主表时使用它，并在新的不同值出现时更新该额外表(如果可能的话)你的设计)。