Re: Window function optimisation, allow pushdowns of items matching PARTITION BY clauses

Hello David,

thanks for your work. The results look promising.

What I'm missing is a test case with multiple fields in the partition by clauses:

-- should push down, because partid is part of all PARTITION BY clauses
explain analyze select partid,n,m from (
  select partid,
  count(*) over (partition by partid) n,
  count(*) over (partition by partid, partid+0) m
  from winagg
) winagg
where partid=1;

current production 9.3.4 is returning


                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on winagg  (cost=350955.11..420955.11 rows=20 width=20) (actual time=2564.360..3802.413 rows=20 loops=1)

   Filter: (winagg.partid = 1)
   Rows Removed by Filter: 1999980

   ->  WindowAgg  (cost=350955.11..395955.11 rows=2000000 width=4) (actual time=2564.332..3657.051 rows=2000000 loops=1)
         ->  Sort  (cost=350955.11..355955.11 rows=2000000 width=4) (actual time=2564.320..2802.444 rows=2000000 loops=1)
               Sort Key: winagg_1.partid, ((winagg_1.partid + 0))
               Sort Method: external sort  Disk: 50840kB
               ->  WindowAgg  (cost=0.43..86948.43 rows=2000000 width=4) (actual time=0.084..1335.081 rows=2000000 loops=1)
                     ->  Index Only Scan using winagg_partid_idx on winagg winagg_1  (cost=0.43..51948.43 rows=2000000 width=4) (actual time=0.051..378.232 rows=2000000 loops=1)
                           Heap Fetches: 0

"Index Only Scan" currently returns all rows (without pushdown) on current production 9.3.4. What happens with the patch you provided?

-- Already Part of your tests:
-- should NOT push down, because partid is NOT part of all PARTITION BY clauses
explain analyze select partid,n,m from (
  select partid,
  count(*) over (partition by partid) n,
  count(*) over (partition by partid+0) m
  from winagg
) winagg
where partid=1;

Reordering the fields should also be tested:
-- should push down, because partid is part of all PARTITION BY clauses
-- here: partid at the end
explain analyze select partid,n,m from (
  select partid,
  count(*) over (partition by partid) n,
  count(*) over (partition by partid+0, partid) m
  from winagg
) winagg
where partid=1;

-- should push down, because partid is part of all PARTITION BY clauses
-- here: partid in the middle
explain analyze select partid,n,m from (
  select partid,
  count(*) over (partition by partid) n,
  count(*) over (partition by partid+0, partid, partid+1) m
  from winagg
) winagg
where partid=1;