I recently discovered an unexpected behavior in PostgreSQL involving a pattern of using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items from a queue-like table. What seemed straightforward turned out to have a surprising interaction with the query planner.The scenarioLet’s say you have a task_queue table and want to pull exactly one task for a specific queue_group_id. A common approach is using a CTE:-- The seemingly straightforward query WITH dele...