Re: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Nyasha Chigwamba <nyasha(dot)chigwamba(at)voss-solutions(dot)com>
Cc: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)
Date: 2026-02-11 17:37:38
Message-ID: 4119091.1770831458@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Nyasha Chigwamba <nyasha(dot)chigwamba(at)voss-solutions(dot)com> writes:
> Question: Can the planner push predicates on view columns into the underlying 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2026-02-12 19:25:03 Re: proposal: schema variables
Previous Message Nyasha Chigwamba 2026-02-11 16:12:31 LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)