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