Subject: Re: Removing unneeded self joins
To: Hywel Carver <hywel@skillerwhale.com>
Cc: pgsql-hackers@lists.postgresql.org,
 Konstantin Knizhnik <knizhnik@garret.ru>
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru>
 <21e15759-dba5-2d2b-dd03-06943ad40378@postgrespro.ru>
 <161553990603.28624.7702383984090736980.pgcf@coridan.postgresql.org>
 <2a6e540a-4c9f-9402-2e02-71e35fcd4dd1@postgrespro.ru>
 <CAFcA2FauZ2jGka8utvFmRE1eLrKfmJmPnQjtf_cdewChFu0qcQ@mail.gmail.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <4b69ea09-5047-cd8a-a0d7-de1f12120433@postgrespro.ru>
Date: Mon, 5 Jul 2021 16:20:01 +0300
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0)
 Gecko/20100101 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFcA2FauZ2jGka8utvFmRE1eLrKfmJmPnQjtf_cdewChFu0qcQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4b69ea09-5047-cd8a-a0d7-de1f12120433%40postgrespro.ru>
Precedence: bulk

On 2/7/21 01:56, Hywel Carver wrote:
> On Wed, Jun 30, 2021 at 12:21 PM Andrey Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.ru>> wrote:
>     I think, here we could ask more general question: do we want to
>     remove a
>     'IS NOT NULL' clause from the clause list if the rest of the list
>     implicitly implies it?
> 
> 
> My suggestion was not to remove it, but to avoid adding it in the first 
> place. When your optimisation has found a join on a group of columns 
> under a uniqueness constraint, you would do something like this (forgive 
> the pseudo-code)
> 
> foreach(column, join_clause) {
>    if(column.nullable) { // This condition is what I'm suggesting is added
>      add_null_test(column, IS_NOT_NULL);
>    }
> }
> 
> But it may be that that's not possible or practical at this point in the 
> code.
I think, such option will require to implement a new machinery to prove 
that arbitrary column couldn't produce NULL value.

-- 
regards,
Andrey Lepikhov
Postgres Professional