Re: Proposal: QUALIFY clause

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