Palle Girgensohn <girgen@pingpong.net> writes:
> How come the planner treats the
> delete from table where not extists(select 1 from table2 where ... LIMIT 1)
> so differently, and usually badly, when the LIMIT 1 is there.
Because it can't optimize it into an antijoin.
> In older
> version of postgresql, I remember that the effect was the opposite, a
> limit 1 would actually perform substantially better. Hence we have old
> code (and old habits), where the LIMIT 1 is still used.
Well, you're basically forcing it into the same type of plan you would
have gotten before antijoins were implemented (circa 8.4), so I don't
see that this is a regression. But I'd get rid of the LIMIT 1 if I were
you. It's been a *very* long time since that was a net benefit in an
EXISTS subquery, if indeed it ever was --- AFAIR, even the earliest PG
versions that understood about optimizing for fast-start plans would do
so in an EXISTS subquery, with or without any LIMIT.
regards, tom lane