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
Date: Fri, 01 Oct 2021 09:56:45 -0400
Message-ID: <3312647.1633096605@sss.pgh.pa.us> (raw)
In-Reply-To: <17207-5265aefa79e333b4@postgresql.org>
References: <17207-5265aefa79e333b4@postgresql.org>
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
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, sema@sema.in, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17207: Bad cost estimate of Merge Join despite correct row estimate
In-Reply-To: <3312647.1633096605@sss.pgh.pa.us>
* 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