Date: Sat, 15 May 2021 19:10:48 +0300
From: Eugen Konkov <kes-kes@yandex.ru>
Message-ID: <147634374.20210515191048@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
CC: PG Bug reporting form <noreply@postgresql.org>,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 pgsql-performance <pgsql-performance@postgresql.org>,
 "ladayaroslav@yandex.ru" <ladayaroslav@yandex.ru>
Subject: Re: BUG #16968: Planner does not recognize optimization
In-Reply-To: 
 <CAApHDvo0=DExJevFU_JjQQVMqUjCAd5e8sc3=rk4d-TbfC7YAw@mail.gmail.com>
References: <16968-9c8a1708c1c04355@postgresql.org>
 <846547866.20210416222735@yandex.ru>
  <226821618.20210513163513@yandex.ru>
  <CAApHDvrayMkhak9eYbhHs67XvkZSOiscxJEER2yECN-k4L91oQ@mail.gmail.com>
  <55211620994674@mail.yandex.ru>
  <CAApHDvo0=DExJevFU_JjQQVMqUjCAd5e8sc3=rk4d-TbfC7YAw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/147634374.20210515191048%40yandex.ru>
Precedence: bulk

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@yandex.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