Jeff Janes <jeff.janes@gmail.com> writes:
> A hashed SubPlan will not be used if it would need more than one
> batch. Is there a fundamental reason for that, or just that no one
> got around to adding it?
It can't, really. Batching a hash join requires freedom to reorder the
rows on both sides of the join. A SubPlan, by definition, must deliver
the correct answer for the current outer row on-demand.
The only real fix for your problem would be to teach the regular hash
join machinery how to handle NOT IN semantics accurately, so that we
could transform this query into a regular kind of join instead of a
seqscan with a SubPlan wart attached to it. In the past it hasn't
really seemed worth it, since 99% of the time, once you question
somebody about why they're insisting on NOT IN rather than NOT EXISTS,
you find out that they didn't really want NOT IN semantics after all.
We could also consider adding logic to notice NOT NULL constraints on
the inner select's outputs, which would allow the planner to prove that
the query can be transformed to a regular antijoin. That only helps
people who've put on such constraints though ...
> I have no control over the real query itself
Sigh. Another badly-written ORM, I suppose?
regards, tom lane