From: Tom Lane <tgl@sss.pgh.pa.us>
To: WU Yan <4wuyan@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Wasteful nested loop join when there is `limit` in the query
In-reply-to: <CAAdwFAwm6HwXM_cuPWZBxrxX4E7pBdVg=KcVDSP6q9ume3hYpQ@mail.gmail.com>
References: <CAAdwFAwm6HwXM_cuPWZBxrxX4E7pBdVg=KcVDSP6q9ume3hYpQ@mail.gmail.com>
Comments: In-reply-to WU Yan <4wuyan@gmail.com>
	message dated "Mon, 17 Feb 2025 17:38:49 +1100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <744078.1739775701.1@sss.pgh.pa.us>
Date: Mon, 17 Feb 2025 02:01:41 -0500
Message-ID: <744079.1739775701@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/744079.1739775701%40sss.pgh.pa.us>
Precedence: bulk

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