Shayon Mukherjee：PostgreSQL规划器在使用CTE、DELETE和LIMIT时的意外行为
💡
原文英文，约900词，阅读约需4分钟。
📝
内容提要
在PostgreSQL中，使用公共表表达式（CTE）与DELETE ... RETURNING和LIMIT结合时，可能会出现意外行为。尽管使用LIMIT 1，查询有时仍会返回多个item_id。这是因为查询规划器选择了半连接优化策略，导致LIMIT在每个候选行上单独执行。为确保LIMIT全局适用，建议在WHERE子句中使用简单的子查询。使用CTE时需谨慎，特别是在DELETE和LIMIT结合时。
🎯
关键要点
- 在PostgreSQL中，使用公共表表达式（CTE）与DELETE ... RETURNING和LIMIT结合时，可能会出现意外行为。
- 尽管使用LIMIT 1，查询有时仍会返回多个item_id，这是因为查询规划器选择了半连接优化策略。
- 在半连接计划中，子查询（包括LIMIT 1）会针对外部扫描找到的每个候选行单独执行。
- 这种行为的间歇性是由于查询规划器的选择依赖于表统计信息、数据分布和内部启发式。
- 为确保LIMIT全局适用，建议在WHERE子句中使用简单的子查询，而不是CTE。
- 使用CTE时需谨慎，特别是在DELETE和LIMIT结合时，可能导致意外的结果。
❓
延伸问答
在PostgreSQL中，使用CTE与DELETE和LIMIT时可能出现什么问题？
使用CTE与DELETE和LIMIT时，可能会出现意外行为，例如尽管使用LIMIT 1，查询仍可能返回多个item_id。
为什么在使用LIMIT 1时，查询会返回多个item_id？
这是因为查询规划器选择了半连接优化策略，导致LIMIT在每个候选行上单独执行。
如何确保LIMIT在PostgreSQL查询中全局适用？
建议在WHERE子句中使用简单的子查询，而不是CTE，以确保LIMIT全局适用。
CTE在PostgreSQL中有什么限制？
CTE并不总是优化的界限，规划器可能会选择内联或以其他方式转换查询，导致意外行为。
如何重构查询以避免CTE导致的问题？
可以通过直接在WHERE子句中使用简单的子查询来重构查询，避免使用CTE。
为什么CTE的行为在不同运行中可能会有所不同？
CTE的行为可能因表统计信息、数据分布和内部启发式的变化而有所不同，导致间歇性问题。
🏷️
标签
➡️
