Hello David, > I really think you're driving yourself down a difficult path by > expecting queries with whole-row vars to be optimised just as well as > using select * or explicitly listing the columns. Yes, I was expect that. I use whole-row because do not want repeat all 10+ columns at select. I do not use (row1).*, (row2).*, because rows could have same columns. eg: row1.name, row2.name both will be named as 'name' and then I can not distinguish them. So I select whole-row and put myself into problems (( It would be nice if (row1).** will be expanded to: row1_id, row1_name etc. But this is other question which I already ask at different thread. Saturday, May 15, 2021, 5:59:41 PM, you wrote: > On Sat, 15 May 2021 at 00:39, KES <kes-kes@xxxxxxxxx> wrote: >> >> Thank you for detailed explanation. I glad to hear that I can use aliases and this will be recognized and optimization is applied. >> >> >We'd need some sort of ability to assign ressortgroupref to a particular column within a >> whole-row var >> Could it be possible to create hidden alias in same way as I did that manually? >> >> Algorithm seems not complex: >> 1. User refer column from composite type/whole-row: (o).agreement_id >> 2. Create hidden column at select: _o_agreement_id >> 3. Replace other references to (o).agreement_id by _o_agreement_id >> 4. Process query as usual after replacements > Internally Postgresql does use a hidden column for columns that are > required for calculations which are not in the SELECT list. e.g ones > that are in the GROUP BY / ORDER BY, or in your case a window > function's PARTITION BY. We call these "resjunk" columns. The problem > is you can't reference those from the parent query. If you explicitly > had listed that column in the SELECT clause, it won't cost you > anything more since the planner will add it regardless and just hide > it from you. When you add it yourself you'll be able to use it in the > subquery and you'll be able to filter out the partitions that you > don't want. > I really think you're driving yourself down a difficult path by > expecting queries with whole-row vars to be optimised just as well as > using select * or explicitly listing the columns. > David -- Best regards, Eugen Konkov
