为什么 PostgreSQL 中复合主键的表上 SELECT DISTINCT 很慢，如何优化？

PostgreSQL 中使用复合主键的表上 SELECT DISTINCT 查询速度慢的原因及优化方法

在 PostgreSQL 数据库中，SELECT DISTINCT 查询的执行速度取决于表结构和数据分布。虽然 tickers 表中的 product_id 列是复合主键的一部分，因此在其上建立了索引，但使用 SELECT DISTINCT product_id FROM tickers 获取唯一 product_id 的查询默认情况下会执行顺序扫描。

性能慢的原因

性能慢的主要原因是表中存在 product_id 的重复值。这意味着对于每个检索到的唯一 product_id，PostgreSQL 必须扫描整个表以确保不存在重复项。

解决方法：模拟索引跳跃扫描

由于 PostgreSQL 尚未原生支持索引跳跃扫描，因此可以使用递归 CTE (公共表表达式) 来模拟此行为。此 CTE 迭代地检索并丢弃重复项，有效地过滤掉除每个唯一 product_id 的一个实例之外的所有实例。

改进后的解决方案

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- 括号必需
   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
   )
TABLE  cte;</code>

此查询使用横向连接来遍历排序后的表，并使用 orderBy 检索唯一的 product_id 值。

结论

通过使用 CTE 方法模拟索引跳跃扫描，可以显著提高 SELECT DISTINCT product_id 查询的执行时间，从而减少从 tickers 表检索唯一 product_id 所需的时间。

以上是为什么 PostgreSQL 中复合主键的表上 SELECT DISTINCT 很慢，如何优化？的详细内容。更多信息请关注PHP中文网其他相关文章！