Message-ID: <gh-pgjdbc-pgjdbc-3511-c2916465050@github.com>
From: "rdhzl (@rdhzl)" <noreply+rdhzl@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Wed, 28 May 2025 13:58:30 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #3511: Performance Regression in JDBC Driver 42.7.5 - getCrossReference
In-Reply-To: <gh-pgjdbc-pgjdbc-3511@github.com>
References: <gh-pgjdbc-pgjdbc-3511@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: rdhzl
X-GitHub-Comment-Id: 2916465050
X-GitHub-Comment-Type: issue_comment
X-GitHub-Edited-At: 2025-05-28T14:13:09Z
X-GitHub-Issue: 3511
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3511#issuecomment-2916465050
Content-Type: text/plain; charset=utf-8

@davecramer wanted to follow up now that I see `42.7.6` was released.

I've got a Liquibase changlog like so:

```
    <changeSet id="PLAT-1997-1-fkey-rename" author="rdhzl">
        <preConditions onFail="MARK_RAN">
            <!-- Ensure the expected constraint exists and the desired constraint name does not -->
            <foreignKeyConstraintExists schemaName="hippo"
                                        foreignKeyName="ext_referral_x_ext_resource_history_ext_referral_history_id_fke"
            />
            <not>
                <foreignKeyConstraintExists schemaName="hippo"
                                            foreignKeyName="erxerh_ext_referral_history_id_erh_id_fkey"
                />
            </not>
        </preConditions>
        <sql>
            <![CDATA[
                ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
            ]]>
        </sql>
        <rollback/> <!--declining the rollback -->
    </changeSet>
```

With the `42.7.4` version of the driver this takes approx 900ms to execute:
```
Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:18:00] INFO [liquibase.ui] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:18:00] FINE [liquibase.changelog] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:18:00] FINE [liquibase.executor] Changeset db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:18:01] INFO [liquibase.snapshot] Creating snapshot
[2025-05-28 09:18:01] FINE [liquibase.changelog] Reading ChangeSet: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:18:01] FINE [liquibase.executor] Executing Statement: 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:18:01] FINE [liquibase.executor] 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:18:01] INFO [liquibase.changelog] Custom SQL executed
[2025-05-28 09:18:01] INFO [liquibase.changelog] ChangeSet db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl ran successfully in 943ms
```

With versions 42.7.5/42.7.6 it takes approx 9000ms to execute, so about a 10x difference:
```
Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:15:52] INFO [liquibase.ui] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:15:52] FINE [liquibase.changelog] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:15:52] FINE [liquibase.executor] Changeset db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:16:02] INFO [liquibase.snapshot] Creating snapshot
[2025-05-28 09:16:02] FINE [liquibase.changelog] Reading ChangeSet: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:16:02] FINE [liquibase.executor] Executing Statement: 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:16:02] FINE [liquibase.executor] 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:16:02] INFO [liquibase.changelog] Custom SQL executed
[2025-05-28 09:16:02] INFO [liquibase.changelog] ChangeSet db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl ran successfully in 9931ms
```

FWIW: If I remove the Liquibase pre-conditions from the changelog the execution time drops to approx 1ms with any version of the driver:
```
Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:52:13] INFO [liquibase.ui] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:52:13] FINE [liquibase.changelog] Running Changeset: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:52:13] FINE [liquibase.executor] Changeset db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:52:13] FINE [liquibase.changelog] Reading ChangeSet: db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl
[2025-05-28 09:52:13] FINE [liquibase.executor] Executing Statement: 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:52:13] FINE [liquibase.executor] 
ALTER TABLE hippo.ext_referral_x_ext_resource_history
                RENAME CONSTRAINT ext_referral_x_ext_resource_history_ext_referral_history_id_fke
                TO erxerh_ext_referral_history_id_erh_id_fkey
[2025-05-28 09:52:13] INFO [liquibase.changelog] Custom SQL executed
[2025-05-28 09:52:13] INFO [liquibase.changelog] ChangeSet db.changelog-v50.0.xml::PLAT-1997-1-fkey-rename::rdhzl ran successfully in 0ms
```

Given the 10x difference in perf between `42.7.4`->`42.7.5`->`42.7.6` there's anything else that might be worth considering here? If there's additional info/testing I can try to help with I'll do my best. Thanks for your time!

I'm running with Liquibase 4.32.0 against PG16. As above this happens during a complete recreation of our schema.