Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1vqE9p-006wMl-3B
	for pgsql-performance@arkaria.postgresql.org;
	Wed, 11 Feb 2026 17:37:47 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1vqE9o-004tcd-3B
	for pgsql-performance@arkaria.postgresql.org;
	Wed, 11 Feb 2026 17:37:45 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vqE9o-004tcK-28
	for pgsql-performance@lists.postgresql.org;
	Wed, 11 Feb 2026 17:37:45 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.98.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vqE9n-00000000Bcz-0Bcp
	for pgsql-performance@lists.postgresql.org;
	Wed, 11 Feb 2026 17:37:45 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 61BHbcvl4119092;
	Wed, 11 Feb 2026 12:37:38 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nyasha Chigwamba <nyasha.chigwamba@voss-solutions.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: LATERAL subquery predicate pushdown: filter applied after JSON
 construction instead of inside join (PG 18)
In-reply-to: 
 <CH0PR10MB4908BD90B3C5155DF98D88A2B463A@CH0PR10MB4908.namprd10.prod.outlook.com>
References: 
 <CH0PR10MB4908BD90B3C5155DF98D88A2B463A@CH0PR10MB4908.namprd10.prod.outlook.com>
Comments: In-reply-to Nyasha Chigwamba <nyasha.chigwamba@voss-solutions.com>
	message dated "Wed, 11 Feb 2026 16:12:31 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4119090.1770831458.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 11 Feb 2026 12:37:38 -0500
Message-ID: <4119091.1770831458@sss.pgh.pa.us>
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/4119091.1770831458%40sss.pgh.pa.us>
Precedence: bulk

Nyasha Chigwamba <nyasha.chigwamba@voss-solutions.com> writes:
> Question: Can the planner push predicates on view columns into the under=
lying LATERAL subqueries?

It can, but it will not push them into a subquery with LIMIT,
because that would potentially change the subquery result.

I'd try to get rid of all those ORDER BY LIMIT bits in your
view definition.  That's generally considered an antipattern
in SQL.  It's definitely an optimization fence.

			regards, tom lane