Message-ID: <dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel@riseup.net>
Subject: Re: Merge David and Goliath tables efficiently
From: nicolas paris <nicolas.paris@riseup.net>
To: pgsql-performance@lists.postgresql.org
Date: Mon, 19 Jun 2023 14:20:26 +0200
In-Reply-To: <27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
References: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
	 <27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel%40riseup.net>
Precedence: bulk

> IMHO the thing that breaks it is the ORDER BY in the merge, which
> likely
> acts as an optimization fence and prevents all sorts of smart things
> including the partitionwise join. I'd bet that if Nicolas replaces
>=20
> =C2=A0 MERGE INTO "goliath" ca
> =C2=A0 USING (SELECT * FROM "david" ORDER BY "list_id") AS t
> =C2=A0 .

Sorry if it was not clear, however there is no order by in the 2.1
strategy. Then this cannot be the reason of not triggering the optim.

For information I do enable partition join feature with jdbc call just
before the merge:
set enable_partitionwise_join=3Dtrue