postgresql views + regexp -> 优化器问题？

Question

我可以从几个表中选择数据，并在开始使用正则表达式时注意到性能问题

执行简单查询可能需要大约 30 秒(仅返回 200 行...)

SELECT *  
FROM  api.view1
JOIN ....
WHERE ....
      AND num ~ '^124456[67]$';

我注意到有问题的部分是“num ~ '^124456[67]$';”

仅将其重写为将时间缩短到几毫秒!

with x as (
SELECT *  
    FROM  api.view1
    JOIN ....
    WHERE ....
)
select * from x WHERE num ~ '^124456[67]$';

如果我从 View 中退出并使用相同的查询和正则表达式 - 一切正常。

所以在我看来它是优化器问题(除非我在这里没有做错什么)

有人可以建议如何告诉优化器走正确的路吗？

**** 编辑 ****

请注意，正则表达式只是一个例子，我知道我可以在上面的例子中放弃正则表达式，但我还有更复杂的表达式。

Answer 1

据我所知，Postgres 实际上实现了所有公用表表达式。因此，性能的提高可能与原始查询的 WHERE 子句的所有过滤在您应用正则表达式过滤器之前已经发生有关。

这是一种可以重写查询的方法，它的性能可能比完整的正则表达式版本要好一些:

SELECT *  
FROM api.view1
JOIN ....
WHERE ....
    AND num IN (1244566, 1244567);

请注意，您实际上不需要在这里使用正则表达式。相反，尝试将 num 与您期望的两个文字值进行比较。上述方法的另一个优点是它可能为要使用的索引打开大门，这可以使查询更快。您当前的正则表达式方法可能会排除索引，至少对于 num 列。