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