我有一个包含数百万行的表,我想查找在特定列中具有所提供的数千个值列表中的任何一个的所有行。基本上,我想运行一个IN(...set...)
查询,该查询在内部重写为一个= ANY(...array...)
构造,数组大小为数千,针对具有数百万行的索引列。
我的问题是:
- 此查询类型中的集合或数组的大小是否有限制?
- 此查询类型如何扩展?我假设数组没有索引,所以大概每个数组值都会命中索引,从而
O(n log N)
为n
数组值和N
表行提供 , 的缩放? - 在一系列更简单的查询中提交这些类型的大型查询会对查询吞吐量造成多大影响?换句话说,是否可以将其分解为几十个单独的查询,每个查询包含 100 个数组值,以便允许该查询的工作与其他查询交错?
限制附录将告知您查询参数的最大数量为 65535,如果内存允许,一条消息(查询)的限制为半 GB。
当然,随着列表变长,性能会逐渐恶化。我建议发送单个数组参数而不是数千个单独的值。另一种方法是将
COPY
值放入临时表中并与其连接。对于正常的列表大小,我没有看到任何优势,但它避免了限制,并且可能对大型列表有益。最后,您必须自己进行基准测试。如果您需要这样的庞大列表,您可能需要重新评估您的设计选择。
我做了一个小基准。
源代码在pastebin上
测试表:10M 行(id INT PRIMARY KEY, s TEXT)。
结果:
解释:
“WHERE id IN (...)”和“WHERE id =ANY(...)”之间没有区别。
假设正在搜索的列已建立索引,它会对数组中的每个值执行一次索引查找,成本为 O(log N)。对于 n 个数组值,总成本为 O(n log N)。正如预期的那样,运行查询的固定成本很小,然后它会随着返回的行数呈线性扩展。
我包括了两种情况:“相关”,其中检索到的行的 id 是连续的;“随机”,其中它们在整个表中随机化。正如预期的那样,各种缓存(从 CPU L1 到操作系统磁盘缓存)都会完成其工作,因此通过更高的引用局部性检索数据会更快。
不管怎样,在每行 2 微秒的情况下,数据库 CPU 负载相当低。
但是,它在 SSD 上运行,并且表缓存在 RAM 中。在更“现实世界”的情况下,表的某些部分不会被缓存,如果您检索随机行,则可以预期每行有一次随机访问。这可能会很慢,具体取决于你的硬件,但是......这与 postgres 本身无关。这完全取决于您的 IO 系统以及数据的缓存情况。如果您使用旋转磁盘并且数据未缓存,并且您并不特别关心此查询是否尽可能快,那么将其切成较小的行列表可能会减少磁盘垃圾。
我还包括了第三个测试用例:
当数组的长度非常大时,其他查询只需进行并行的 seq 扫描。这非常快,因为“Filter where id=ANY(...)”并不愚蠢,它使用某种快速搜索,如散列或二等分,它不会将每一行与数组的每个值进行比较。
最后一个查询很有趣,因为它是一个联接,因此 postgres 将其优化为联接,在某些情况下可能会更快...或更慢...。