主持人 > 博客 > 管理 > PostgreSQL 反模式：CTE x CTE

PostgreSQL 反模式：CTE x CTE

由于我的工作原因，我必须处理开发人员编写请求并认为“底座很聪明，它可以自己处理一切！«

在某些情况下（部分是由于对数据库功能的无知，部分是由于过早的优化），这种方法会导致“弗兰肯斯坦”的出现。

首先，我举一个这样的请求的例子：

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

为了实质性地评估请求的质量，让我们创建一些任意数据集：

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

事实证明 读取数据花费了不到四分之一的时间 查询执行：

PostgreSQL 反模式：CTE x CTE[看看 explain.tensor.ru]

把它一块一块地拆开

我们仔细看看请求，不解：

  1. 如果没有递归 CTE，为什么这里会出现“WITH RECURSIVE”？
  2. 如果最小/最大值无论如何都与原始样本相关联，为什么还要将它们分组到单独的 CTE 中呢？
    +25% 时间
  3. 为什么在末尾使用无条件“SELECT * FROM”来重复之前的 CTE？
    +14% 时间

在这种情况下，我们非常幸运的是选择了 Hash Join，而不是 Nested Loop 来进行连接，因为那样我们将不仅收到一个 CTE Scan pass，而是收到 10K 个！

关于 CTE 扫描的一些信息这里我们必须记住一点 CTE 扫描与 Seq 扫描类似 - 也就是说，没有索引，而只是完整的搜索，这需要 10K x 0.3 毫秒 = 3000ms 对于 cte_max 的周期 или 1K x 1.5 毫秒 = 1500ms 当通过 cte_bind 循环时!
其实你想得到什么结果呢？ 是的，通常这是在分析“三层”查询的第五分钟时出现的问题。

我们想要输出每个唯一的密钥对 key_a 分组的最小值/最大值.
所以让我们用它来做这个 窗函数:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

PostgreSQL 反模式：CTE x CTE
[看看 explain.tensor.ru]

由于读取两个选项中的数据大约需要 4-5 毫秒，因此我们所有的时间都增加了 -32％ - 这是最纯粹的形式 从基础 CPU 移除负载，如果这样的请求执行得足够频繁。

一般来说，你不应该强迫底座“承载圆的，滚动方的”。

来源： habr.com

添加评论