Message-ID: <6ed3a29e-b4b9-e4b6-e8a1-b1d7d00bbd88@postgrespro.ru>
Date: Mon, 26 Jun 2023 15:22:03 +0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.12.0
Subject: Re: Problems with estimating OR conditions, IS NULL on LEFT JOINs
To: Tomas Vondra <tomas.vondra@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <52b55b53-420a-722c-adbd-706922fc059b@enterprisedb.com>
 <104950.1687565314@sss.pgh.pa.us>
 <6dce55f4-e8f6-8e8e-b2af-b45ccff13598@enterprisedb.com>
Content-Language: en-US
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <6dce55f4-e8f6-8e8e-b2af-b45ccff13598@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/6ed3a29e-b4b9-e4b6-e8a1-b1d7d00bbd88%40postgrespro.ru>
Precedence: bulk

On 24/6/2023 17:23, Tomas Vondra wrote:
> I really hope what I just wrote makes at least a little bit of sense.
Throw in one more example:

SELECT i AS id INTO l FROM generate_series(1,100000) i;
CREATE TABLE r (id int8, v text);
INSERT INTO r (id, v) VALUES (1, 't'), (-1, 'f');
ANALYZE l,r;
EXPLAIN ANALYZE
SELECT * FROM l LEFT OUTER JOIN r ON (r.id = l.id) WHERE r.v IS NULL;

Here you can see the same kind of underestimation:
Hash Left Join  (... rows=500 width=14) (... rows=99999 ...)

So the eqjoinsel_unmatch_left() function should be modified for the case 
where nd1<nd2.

-- 
regards,
Andrey Lepikhov
Postgres Professional