Message-ID: <36cb9fe9-b792-2345-65ed-586de44a48b9@enterprisedb.com>
Date: Mon, 19 Jun 2023 15:13:39 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.1
Subject: Re: Merge David and Goliath tables efficiently
Content-Language: en-US
To: nicolas paris <nicolas.paris@riseup.net>,
 pgsql-performance@lists.postgresql.org
References: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
 <27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
 <dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel@riseup.net>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel@riseup.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/36cb9fe9-b792-2345-65ed-586de44a48b9%40enterprisedb.com>
Precedence: bulk

On 6/19/23 14:20, nicolas paris wrote:
>> 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
>>   .
> 
> 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=true
> 

But you wrote that in both cases the query is:

    MERGE INTO "goliath" ca
    USING (SELECT * FROM "david" ORDER BY "list_id") AS t
    ON t."list_id" = ca."list_id"
    WHEN MATCHED THEN
    UPDATE SET ...
    WHEN NOT MATCHED THEN
    INSERT (...)
    VALUES (...)

With all due respect, I'm getting a bit tired of having to speculate
about what exactly you're doing etc. based on bits of information.

I'm willing to continue to investigate, but only if you prepare a
reproducer, i.e. a SQL script that demonstrates the issue - I don't
think preparing that should be difficult, something like the SQL script
I shared earlier today should do the trick.

I suggest you do that directly, not through JDBC. Perhaps the JDBC
connection pool does something funny (like connection pooling and
resetting settings).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company