Re: Why does delete from table where not exists (select 1 from ... LIMIT 1) perform badly?

Palle Girgensohn <girgen(at)pingpong(dot)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