为什么我的 SELECT DISTINCT 查询在具有复合主键的 Postgres 表上运行缓慢，如何提高其性能？

优化 Postgres 中缓慢的 SELECT DISTINCT 查询

本文解决了在具有复合主键的大型 Postgres 表上执行 SELECT DISTINCT 查询时遇到的性能问题。 检查了涉及具有近 200 万行和复合主键（product_id、trade_id）的表的特定场景。 虽然由于主键索引，SELECT DISTINCT product_id 查询在理想情况下应该很快，但我们却观察到性能出乎意料地缓慢。

根本原因分析：

查询规划器选择顺序扫描而不是利用索引，被认为是瓶颈。这是由于表的数据分布造成的：只有40个唯一的产品ID，导致索引值重复程度很高。 这会导致大量索引探测和低效的顺序访问。

有效的解决方案：递归CTE

为了规避此限制并有效利用索引，提出了递归公用表表达式 (CTE) 作为 SELECT DISTINCT:

的更好替代方案

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses required
   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  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;</code>

这种递归 CTE 有效地模仿了索引跳跃扫描。它按排序顺序迭代检索不同的 product_id 值，从而避免与低效顺序扫描相关的性能损失。 在 product_id 列上使用索引对于此方法的最佳性能至关重要。

重要提示：虽然 Postgres 的索引跳过扫描功能正在开发中，但这种基于 CTE 的解决方案为所描述的场景提供了强大且高效的解决方案，显着提高了查询性能。

以上是为什么我的 SELECT DISTINCT 查询在具有复合主键的 Postgres 表上运行缓慢，如何提高其性能？的详细内容。更多信息请关注PHP中文网其他相关文章！