On Fri, 14 May 2021 at 02:38, Eugen Konkov <kes-kes@xxxxxxxxx> wrote: > Now I create minimal reproducible test case. > https://dbfiddle.uk/?rdbms=postgres_13&fiddle=761a00fb599789d3db31b120851d6341 > > Optimization is not applyed when I filter/partition by column using composite type name. You probably already know this part, but let me explain it just in case it's not clear. The pushdown of the qual from the top-level query into the subquery, or function, in this case, is only legal when the qual references a column that's in the PARTITION BY clause of all window functions in the subquery. The reason for this is, if we filter rows before calling the window function, then it could affect which rows are in see in the window's frame. If it did filter, that could cause incorrect results. We can relax the restriction a bit if we can eliminate entire partitions at once. The window function results are independent between partitions, so we can allow qual pushdowns that are in all PARTITION BY clauses. As for the reason you're having trouble getting this to work, it's down to the way you're using whole-row vars in your targetlist. A slightly simplified case which shows this problem is: create table ab(a int, b int); explain select * from (select ab as wholerowvar,row_number() over (partition by a) from ab) ab where (ab.wholerowvar).a=1; The reason it does not work is down to how this is implemented internally. The details are, transformGroupClause() not assigning a ressortgroupref to the whole-row var. It's unable to because there is no way to track which actual column within the whole row var is in the partition by clause. When it comes to the code that tries to push the qual down into the subquery, check_output_expressions() checks if the column in the subquery is ok to accept push downs or not. One of the checks is to see if the query has windowing functions and to ensure that the column is in all the PARTITION BY clauses of each windowing function. That check is done by checking if a ressortgroupref is assigned and matches a tleSortGroupRef in the PARTITION BY clause. In this case, it does not match. We didn't assign any ressortgroupref to the whole-row var. Unfortunately, whole-row vars are a bit to 2nd class citizen when it comes to the query planner. Also, it would be quite a bit of effort to make the planner push down the qual in this case. We'd need some sort of ability to assign ressortgroupref to a particular column within a whole-row var and we'd need to adjust the code to check for that when doing subquery pushdowns to allow it to mention which columns within whole-row vars can legally accept pushdowns. I imagine that's unlikely to be fixed any time soon. Whole-row vars just don't seem to be used commonly enough to warrant going to the effort of making this stuff work. To work around this, you should include a reference to the actual column in the targetlist of the subquery, or your function, in this case, and ensure you use that same column in the PARTITION BY clause. You'll then need to write that column in your condition that you need pushed into the subquery. I'm sorry if that messes up your design. However, I imagine this is not the only optimisation that you'll miss out on by doing things the way you are. David
