From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: nicolas paris <nicolas.paris@riseup.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
Date: Mon, 19 Jun 2023 13:53:50 +0200
Message-ID: <27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com> (raw)
In-Reply-To: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
References: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
On 6/19/23 09:46, Alvaro Herrera wrote:
> I came here to talk about partitionwise join, but then noticed you have
> already thought of that:
>
> On 2023-Jun-18, nicolas paris wrote:
>
>> Note that both plan acome from the same partitioned by hash table with
>> 100 parts, with a unique index on the list_id + hash_key. For strategy
>> 2.1, I turned on enable_partitionwise_join, since david table has the
>> same partitioning scheme as goliath including unique indexe. In both
>> case the query is:
>
> Hmm, I suppose the reason partitionwise join isn't having any effect is
> that the presence of WHEN NOT MATCHED clauses force an outer join, which
> probably disarms partitionwise joining, since each join pair would
> require to match for nulls, so there would be two matching partitions at
> the other end. A quick test for this hypothesis might be to try the
> MERGE without the WHEN NOT MATCHED clauses and see if partitionwise join
> works better.
>
> Maybe Tom L's new outer-join infrastructure in 16 allows to improve on
> this, not sure.
>
Not sure why would that disarm partitionwise join - attached is a simple
reproducer, generating two tables, loading 10000000 and 10000 rows into
them, and then doing explain on a simple merge.
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
MERGE INTO "goliath" ca
USING (SELECT * FROM "david" ORDER BY "list_id") AS t
..
with
MERGE INTO "goliath" ca
USING "david" AS t
...
it'll start doing the working much better.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[application/sql] repro.sql (18.0K, ../27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com/2-repro.sql)
download
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-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, alvherre@alvh.no-ip.org, nicolas.paris@riseup.net, pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
In-Reply-To: <27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
* 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