From: Tom Lane <tgl@sss.pgh.pa.us>
To: sema@sema.in
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17207: Bad cost estimate of Merge Join despite correct row
 estimate
In-reply-to: <17207-5265aefa79e333b4@postgresql.org>
References: <17207-5265aefa79e333b4@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 30 Sep 2021 23:29:48 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3312646.1633096605.1@sss.pgh.pa.us>
Date: Fri, 01 Oct 2021 09:56:45 -0400
Message-ID: <3312647.1633096605@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3312647.1633096605%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> [ seriously awful mergejoin cost estimate ]

> While it is possible for Merge Join to also finish quickly - if user_id are
> all low numbers - I'm not sure if that's a realistic expectation for the
> default plan.

Yeah, it looks like it's incorrectly adjusting based on a belief that
it won't have to scan all of the join input.

I found that the problem only appears in v11 and later; earlier versions
come out with a more realistic estimate of the cost of the merge.
"git bisect" says it changed at

    7d08ce286cd5854d58152e428c28636a616bdc42 is the first bad commit
    commit 7d08ce286cd5854d58152e428c28636a616bdc42
    Author: Tom Lane <tgl@sss.pgh.pa.us>
    Date:   Wed Sep 13 11:12:39 2017 -0400

        Distinguish selectivity of < from <= and > from >=.

I've not yet figured out what the connection is, but a reasonable
bet is that there's an edge case associated with the ranges of the
two join keys being exactly the same.  Anyway, I'll find it and
fix it.  Thanks for the report!

			regards, tom lane