Re: BUG #16968: Planner does not recognize optimization

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(at)yandex(dot)ru> 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