From: Eugen Konkov <kes-kes@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: PG Bug reporting form <noreply@postgresql.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: ladayaroslav@yandex.ru <ladayaroslav@yandex.ru>
Subject: Re: BUG #16968: Planner does not recognize optimization
Date: Sat, 15 May 2021 19:10:48 +0300
Message-ID: <147634374.20210515191048@yandex.ru> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kes-kes@yandex.ru, dgrowleyml@gmail.com, noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
In-Reply-To: <147634374.20210515191048@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox