为什么我的 PostgreSQL 具有复合主键的 SELECT DISTINCT 查询如此慢？

PostgreSQL SELECT DISTINCT 复合键的性能问题

在具有复合主键（例如 SELECT DISTINCT）的 PostgreSQL 表上使用 (product_id, trade_id) 可能会非常慢。 查询规划器经常选择顺序扫描，而不是有效地利用索引。

为什么这么慢？

• 顺序扫描偏好：规划者可能更喜欢全表扫描，即使有索引，也会导致查询时间显着延长。
• 缺少索引跳过扫描：PostgreSQL 本身不支持索引跳过扫描，这是一种通过跳过索引中的重复项来有效检索唯一值的优化。

解决方案：使用 CTE 模拟索引跳跃扫描

虽然真正的索引跳过扫描不可用，但我们可以使用递归公共表表达式 (CTE) 有效地模仿其行为：

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id
      ORDER  BY 1
      LIMIT  1
      ) l
   )
SELECT * FROM cte;</code>

此 CTE 按排序顺序迭代唯一的 product_id 值，利用 (product_id) 上的索引来提高效率。

这种方法的优点

• 速度改进：此方法大大减少了查询执行时间。 测试显示，在 225 万行表上，时间缩短至 0.75 毫秒。
• 索引利用率：它有效地利用了复合主键索引(product_id, trade_id)和(product_id)上的索引。
• 数据分布不可知：无论表内的数据分布如何，性能都保持一致。

以上是为什么我的 PostgreSQL 具有复合主键的 SELECT DISTINCT 查询如此慢？的详细内容。更多信息请关注PHP中文网其他相关文章！