pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix bit-rotted planner test case.
Date: Sat, 17 Dec 2022 23:52:59 +0000
Message-ID: <E1p6gzC-00491m-TP@gemulon.postgresql.org> (raw)

Fix bit-rotted planner test case.

While fooling with my pet outer-join-variables patch, I discovered
that the test case I added in commit 11086f2f2 no longer demonstrates
what it's supposed to.  The idea is to tempt the planner to reverse
the order of the two outer joins, which would leave noplace to
correctly evaluate the WHERE clause that's inserted between them.
Before the addition of the delay_upper_joins mechanism, it would
have taken the bait.

However, subsequent improvements broke the test in two different ways.
First, we now recognize the IS NULL coding pattern as an antijoin, and
we won't re-order antijoins; even if we did, the IS NULL test clauses
get removed so there would be no opportunity for them to misbehave.
Second, the planner now discovers that nested parameterized indexscans
are a lot cheaper than the double hash join it used back in the day,
and that approach doesn't want to re-order the joins anyway.  Thus,
in HEAD the test passes even if one dikes out delay_upper_joins.

To fix, change the IS NULL tests to COALESCE clauses, which produce
the same results but the planner isn't smart enough to convert them
to antijoins.  It'll still go for parameterized indexscans though,
so drop the index enabling that (don't know why I added that in the
first place), and disable nestloop joining just to be sure.

This time around, add an EXPLAIN to make the choice of plan visible.

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/7122f9d5437789312cb0a7e26e853bb8d2e57add

Modified Files
--------------
src/test/regress/expected/join.out | 35 ++++++++++++++++++++++++++++++++---
src/test/regress/sql/join.sql      | 21 ++++++++++++++++++---
2 files changed, 50 insertions(+), 6 deletions(-)



Message-ID: <E1p6gzC-00491m-TP@gemulon.postgresql.org>
Permalink:  ../E1p6gzC-00491m-TP@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1p6gzC-00491m-TP@gemulon.postgresql.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tgl@sss.pgh.pa.us
  Subject: Re: pgsql: Fix bit-rotted planner test case.
  In-Reply-To: <E1p6gzC-00491m-TP@gemulon.postgresql.org>
  References: <E1p6gzC-00491m-TP@gemulon.postgresql.org>

* 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