30.01.2026 Problem:
The customer reported degraded performance in a PostgreSQL query joining multiple views and tables,
including V_CDD_PRF_PARTY_PARTY_R_DRF. Although the outer query applied a highly selective filter
(C.FIRST_PARTY_KEY = 'CDD000248470'), the PostgreSQL optimizer did not push this predicate into the view. As a result, the view was fully evaluated, leading to increased CPU usage, higher I/O, and longer execution times. The customer suspected a PostgreSQL optimizer defect.
Process:
Step 1: Initial Analysis
We analyzed the original SQL query and confirmed that the filter on FIRST_PARTY_KEY was applied only at the top-level query. We explained that PostgreSQL performs predicate pushdown into views only when it is provably safe and that views containing certain constructs (aggregates, correlated subqueries, OUTER JOINs) may act as optimization barriers. To validate planner behavior, we requested the view definition using pg_get_viewdef() and the execution plan via EXPLAIN (ANALYZE, VERBOSE, BUFFERS).
Step 2: Root Cause Investigation
After reviewing the view DDL and execution plan, we identified a correlated aggregate subquery
in the view definition using MAX(time_from) to select the most recent row per
(first_party_key, second_party_key, relation_type_cd) group.
The aggregate depended on the full set of rows per group, and applying the outer predicate early could change the MAX(time_from) result. For correctness reasons, PostgreSQL treated this construct as an optimization barrier and prevented predicate pushdown. This behavior was confirmed to be expected and compliant with PostgreSQL optimizer rules, not a database bug.
Step 3: Impact and Optimization Assessment
Because the predicate could not be pushed into the view, PostgreSQL scanned a larger portion of
the underlying dataset. This increased CPU and disk I/O usage and resulted in longer query execution times. We evaluated multiple remediation options and determined that only a structural rewrite of the view could safely remove the optimization barrier.
Solution:
We recommended rewriting the view using a set-based approach with the
ROW_NUMBER() window function to replace the correlated MAX(time_from) subquery.
The redesigned view:
- Uses
ROW_NUMBER() OVER (PARTITION BY first_party_key, second_party_key, relation_type_cd ORDER BY time_from DESC)to select the latest row per group. - Eliminates correlated subqueries and optimization fences.
- Enables safe predicate pushdown for filters such as
FIRST_PARTY_KEY. - Preserves functional correctness while improving execution plans and performance.
Alternative approaches such as DISTINCT ON and index tuning were discussed,
but the window function approach provided the most reliable and scalable solution.
Conclusion:
The performance issue was caused by a correlated aggregate in the view definition,
which PostgreSQL correctly treated as an optimization barrier. By redesigning the view using window functions, we enabled predicate pushdown, reduced resource consumption, and improved query execution efficiency. The customer validated the results and successfully closed the case.