On 22/07/25 19:32, David Rowley wrote: > 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 > By "pushdowns" you mean missing the Run Conditions on the QUALIFY example? IIUC the Run Condition is only created if it's a subquery. I've checked this on set_rel_size() -> set_subquery_pathlist() -> check_and_push_window_quals().
> Also, this seems busted: > > # select row_number() over (order by oid) rn from pg_class qualify rn=1; > server closed the connection unexpectedly > Thanks for testing! I'm working on this and some other issues. -- Matheus Alcantara
