PostgreSQL's Surprise: CTEs, DELETE, and LIMIT's Unexpected Dance

A surprising PostgreSQL behavior emerged when using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items. The intention was to delete only one row, but multiple rows were deleted. `EXPLAIN ANALYZE` revealed a nested loop semi-join optimization, causing the LIMIT 1 clause to be executed multiple times. The solution was to restructure the query, avoiding the CTE and using a subselect directly in the DELETE's WHERE clause. This highlights that CTEs don't always prevent query plan optimizations, and careful plan examination is crucial for critical operations.