主页 / dba / 问题 / 280114
Accepted
Sebastiaan van den Broek
Asked: 2020-11-23 01:26:00 +0800 CST

数据库是否优化同一列上重复的相似查询参数？

  • 772

我有以下查询：

SELECT * FROM table t
WHERE t.id IN :ids
AND t.id IN :allowedIds

（查询参数稍后被替换）

这些查询参数是否由数据库本身优化？我想避免在代码中将两组 id 合并在一起，因为这会导致可读性差。第一个:ids约束是用户自己作为过滤器传递的内容，第二个:allowedIds约束是用户可以访问的内容。所以我希望大多数数据库，尤其是 Postgres，会优化这类事情。

postgresql query-performance

1 个回答

  1. Best Answer

    我将忽略这个问题中的“大多数数据库”，否则我将不得不投票关闭，因为缺乏焦点。相反，我将回答有关 PostgreSQL 的问题。

    要“合并”问题中的列表，您必须构建交集：

    WHERE id IN (1, 2, 3, 4) AND id IN (3, 4, 5, 6)

    是相同的

    WHERE id IN (3, 4)

    PostgreSQL 不会自动执行此操作。PostgreSQL 可以选择扫描现有索引中的一个或两个条件，或者它可以进行顺序扫描，但这两个条件不会合并。

    更重要的是，PostgreSQL 会将这两个条件视为统计上独立的，只是将它们的选择性相乘，这可能会导致错误的估计。

    对于具有单个IN列表的查询，您很可能会做得更好，该列表是原始列表的交集。

    你问为什么 PostgreSQL 团队没有在 PostgreSQL 的代码中添加这种优化，所以我对此有一些评论。这是一个相当不寻常的要求（一开始我误解了这个问题），并且在为此类要求添加优化器代码时总是需要权衡：虽然您的查询会受益，但许多查询将不得不以如下形式付出代价用于测试优化是否适用的额外 CPU 周期。现在查询计划时间是一个相当敏感的区域（它必须很快发生），所以我们通常不愿意为极端情况添加特殊处理，特别是如果它们可以通过重写查询轻松避免的话。

    另请注意：如果您能够将列表作为数组传递并安装允许您计算两个（整数！）数组之间的交集的intarray 扩展，则可以在 SQL 级别进行交集。例如where id = any(:ids & :allowedIds)

    • 3

相关问题