postgresql - Postgres : numrange() breaks under certain plans

Question

此查询过去运行良好，但现在出现问题:

SELECT
    incoming.*,
    prescribed.*
FROM
    "CRM_DEAL_INCOMING_RANGES_M" incoming
LEFT JOIN 
    "CRM_DEAL_PRESCRIBED_RANGES_M" prescribed 
        -- join the two tables
        ON prescribed.klient = incoming.klient 
        AND prescribed.op = incoming.op 
        -- make sure ranges are correct - there really is data where range_to > range_from, we just want to skip it
        AND prescribed.range_from <= prescribed.range_to
        AND incoming.range_from <= incoming.range_to
        AND numrange(prescribed.range_from, prescribed.range_to) 
            && numrange(incoming.range_from, incoming.range_to)
WHERE incoming.klient = 4;

请注意，我们正在确保范围边界的顺序正确。

查询以以下错误结束:错误:范围下限必须小于或等于范围上限。

但是，如果我删除 WHERE 条件，它会工作得很好!

问题的解释是here ，工作替代方案的解释(没有 WHERE)是 here .

当规划器可以移动我的条件时，我如何过滤掉 numrange() 会阻塞的数据？

编辑:现在我只是将连接条件的第二部分从 JOIN 移到 WHERE，但我觉得它只是暂时避开了规划器。

SELECT
    incoming.*,
    prescribed.*
FROM
    "CRM_DEAL_INCOMING_RANGES_M" incoming
LEFT JOIN 
    "CRM_DEAL_PRESCRIBED_RANGES_M" prescribed 
        -- join the two tables
        ON prescribed.klient = incoming.klient 
        AND prescribed.op = incoming.op 
WHERE 
  incoming.klient = 4
  -- make sure ranges are correct - there really is data where range_to > range_from, we just want to skip it
  AND prescribed.range_from <= prescribed.range_to
  AND incoming.range_from <= incoming.range_to
  AND numrange(prescribed.range_from, prescribed.range_to) 
    && numrange(incoming.range_from, incoming.range_to);

(正如 a_horse_with_no_name 正确指出的那样，这与原始查询具有不同的语义——尽管在我的具体情况下这并不重要)

Answer 1

不保证连接项、WHERE 子句等将以任何给定顺序执行。如果您的查询依赖于它，那么它就是错误的。

在这种情况下，正确的解决方案是 a CASE expression ，这(主要)强制排序。

CASE
WHEN prescribed.range_from <= prescribed.range_to
     AND incoming.range_from <= incoming.range_to
     THEN numrange(prescribed.range_from, prescribed.range_to) 
          && numrange(incoming.range_from, incoming.range_to)
END;

请注意，这里不需要 ELSE 子句，因为如果没有子句匹配，CASE 会生成 NULL，而 NULL 对于 WHERE 子句为假。

这也可能会搞砸索引选择，但大多数其他方法也是如此，例如使用 OFFSET 0 的子查询或(ab)使用 CTE。

我认为 numrange 到 ERROR 倒置范围是一个错误特征，而不是像翻转它们或评估为 null 这样的事情，正是因为它使得这样难以编写的查询。