Re: PGOPTIONS="-fh" make check gets stuck since Postgres 11

On Mon, Jul 8, 2019 at 5:53 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> I have begun playing with regressplans.sh which enforces various
> combinations of "-f s|i|n|m|h" when running the regression tests, and
> I have noticed that -fh can cause the server to become stuck in the
> test join_hash.sql with this query (not sure which portion of the SET
> LOCAL parameters are involved) :
> select count(*) from simple r join extremely_skewed s using (id);
>
> This does not happen with REL_10_STABLE where the test executes
> immediately, so we has visibly an issue caused by v11 here.

If you don't allow hash joins it makes this plan:

Aggregate
-> Nested Loop
Join Filter: (r.id = s.id)
-> Seq Scan on simple r
-> Materialize
-> Seq Scan on extremely_skewed s

"simple" has 20k rows and "extremely_skewed" has 20k rows but the
planner thinks it only has 2. So this going to take O(n^2) time and n
is 20k. Not sure what to do about that. Maybe "join_hash" should be
skipped for the -h (= no hash joins please) case?

--
Thomas Munro
https://enterprisedb.com