Re: Wasteful nested loop join when there is `limit` in the query

WU Yan <4wuyan@gmail.com> writes:
> Hello everyone, I am still learning postgres planner and performance
> optimization, so please kindly point out if I missed something obvious.

An index on employee.name would likely help here.  Even if we had
an optimization for pushing LIMIT down through a join (which you
are right, we don't) it could not push the LIMIT through a sort step.
So you need presorted output from the scan of "employee".  I think
this example would behave better with that.  You may also need to
test with non-toy amounts of data to get the plan you think is
better: an example with only half a dozen rows is going to be
swamped by startup costs.

            regards, tom lane