Re: BUG #16968: Planner does not recognize optimization

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