Request a Demo
Home   >   mailing lists

Re: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18) - Mailing list pgsql-performance

From Tom Lane
Subject Re: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)
Date
Msg-id 4119091.1770831458@sss.pgh.pa.us
Whole thread Raw
In response to LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)  (Nyasha Chigwamba <nyasha.chigwamba@voss-solutions.com>)
Responses Re: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)
List pgsql-performance
Tree view 
Nyasha Chigwamba <nyasha.chigwamba@voss-solutions.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

pgsql-performance by date:

Previous
From: Nyasha Chigwamba
Date:
Subject: LATERAL subquery predicate pushdown: filter applied after JSON construction instead of inside join (PG 18)
Next
From: Clive Boughton
Date:
Subject: A serious change in performance between PG 15 and PG 16, 17, 18.