On Wed, 23 Jul 2025 at 09:21, Vik Fearing <vik@postgresfriends.org> wrote:
> I took a quick look at the patch (without applying and testing it) and
> it seems to me that parse analysis is the wrong place to do this. We
> want ruleutils to be able to spew out the QUALIFY clause as written in a
> view and not as transformed. If we are going to go down the syntax
> transformation route, that should happen in the rewriter at
> planning/execution time.
I agree with Tom on not jumping the gun on the standard thing, but if
that does materialise one day, then whichever method is used, you'd
still want the same pushdown optimisations to happen that currently
happen with qual pushdown into subqueries.
Looking at the latest patch I see that pushdowns don't work:
# explain select row_number() over (order by oid) rb from pg_Class
qualify row_number () over (order by oid)=1;
# explain (analyze, costs off, buffers off, summary off) select
row_number() over (order by oid) rb from pg_Class qualify row_number
() over (order by oid)=1;
WindowAgg (actual time=0.041..0.273 rows=1.00 loops=1)
Window: w1 AS (ORDER BY oid ROWS UNBOUNDED PRECEDING)
Filter: (row_number() OVER w1 = 1)
Rows Removed by Filter: 415
Storage: Memory Maximum Storage: 17kB
-> Index Only Scan using pg_class_oid_index on pg_class (actual
time=0.032..0.125 rows=416.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Whereas, with a subquery we get:
# explain (analyze, costs off, buffers off, summary off)
select * from (select row_number() over (order by oid) rn from
pg_class) r where r.rn=1;
Subquery Scan on r (actual time=0.042..0.044 rows=1.00 loops=1)
Filter: (r.rn = 1)
-> WindowAgg (actual time=0.041..0.043 rows=1.00 loops=1)
Window: w1 AS (ORDER BY pg_class.oid ROWS UNBOUNDED PRECEDING)
Run Condition: (row_number() OVER w1 <= 1)
Storage: Memory Maximum Storage: 17kB
-> Index Only Scan using pg_class_oid_index on pg_class
(actual time=0.030..0.031 rows=2.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Also, this seems busted:
# select row_number() over (order by oid) rn from pg_class qualify rn=1;
server closed the connection unexpectedly
David
