Shayon Mukherjee：PostgreSQL规划器在使用CTE、DELETE和LIMIT时的意外行为
内容提要
在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结合时，可能导致意外的结果。
延伸解读
CTE的优化策略
在PostgreSQL中，公共表表达式（CTE）并不总是能强制查询规划器进行物化处理。文章指出，规划器可能会选择半连接优化策略，这会导致LIMIT的应用在每个候选行上单独执行，而不是全局适用。这种行为可能会导致意外的结果，尤其是在涉及DELETE和LIMIT的情况下。
查询计划的重要性
使用EXPLAIN ANALYZE工具可以帮助开发者理解查询的执行策略。文章中提到，通过分析查询计划，可以发现规划器的选择如何影响最终结果。这强调了在进行关键操作时，检查查询计划的重要性，以确保数据库的行为符合预期。
避免意外行为的建议
为了避免CTE与DELETE和LIMIT结合时出现意外行为，建议使用简单的子查询替代CTE。这种结构更明确，通常能促使规划器先评估子查询，从而确保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的行为可能因表统计信息、数据分布和内部启发式的变化而有所不同，导致间歇性问题。
