I've used a CTE before specifically for its optimisation barrier. The query planner wasn't generating the query I knew would be faster, so I re-jiggered it a bit with a CTE and it dropped from 100ms to 1ms, as I expected. I know there's ways to fiddle with the query planner but sometimes it's just easier to tell postgres: "No, do it this way".