Message-ID: <gh-pgjdbc-pgjdbc-3511-c2859821499@github.com>
From: "davecramer (@davecramer)" <noreply+davecramer@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Wed, 07 May 2025 18:32:19 +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: davecramer
X-GitHub-Comment-Id: 2859821499
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3511
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3511#issuecomment-2859821499
Content-Type: text/plain; charset=utf-8

@danielhasan 
Here's the file I am using which is very similar to what you provided 
```<?xml version="1.0" encoding="utf-8" ?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.31.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 
    <property name="first_table_t" value="105" />

    <changeSet id="create-sequence" author="author">
        <createSequence schemaName="ts" sequenceName="seq_first_table" minValue="1" maxValue="1099511627775" cycle="true"/>
    </changeSet>
    <changeSet id="create-table" author="author">
        <createTable schemaName="ts" tableName="user">
	        <column name="id"
                    defaultValueComputed="nextval('ts.seq_first_table')"
                    type="BIGINT">
                <constraints primaryKey="true"/>
            </column>
            <column name="first" type="text"/>
            <column name="last" type="text"/>
	    </createTable>
        <createTable schemaName="ts" tableName="second_table">
            <column name="id"
                    defaultValueComputed="nextval('ts.seq_first_table')"
                    type="BIGINT">
                <constraints primaryKey="true"/>
            </column>
            
            <column name="completion_date" type="TIMESTAMPTZ">
                <constraints nullable="false" />
            </column>
            <column name="created_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
                <constraints nullable="false" />
            </column>
            <column name="created_by_id" type="BIGINT">
                <constraints foreignKeyName="second_table_created_by_id_fkey"
                             referencedTableSchemaName="ts"
                             referencedTableName="user"
                             referencedColumnNames="id"/>
            </column>
            <column name="modified_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
                <constraints nullable="false" />
            </column>
            <column name="modified_by_id" type="BIGINT">
                <constraints foreignKeyName="first_table_modified_by_id_fkey"
                             referencedTableSchemaName="ts"
                             referencedTableName="user"
                             referencedColumnNames="id"/>
            </column>
            <column name="status" type="VARCHAR(128)">
                <constraints nullable="false" />
            </column>
            <column name="invoice_id" type="VARCHAR(64)"></column>
        </createTable>
        
        <createTable schemaName="ts" tableName="first_table">
            <column name="id"
                    defaultValueComputed="nextval('ts.seq_first_table')"
                    type="BIGINT">
                <constraints primaryKey="true"/>
            </column>
            <column name="second_table_id" type="BIGINT">
                <constraints foreignKeyName="first_table_second_table_id_fkey"
                             referencedTableSchemaName="ts"
                             referencedTableName="second_table"
                             referencedColumnNames="id"
                             unique="true"
                             nullable="false"/>
            </column>
            <column name="completion_date" type="TIMESTAMPTZ">
                <constraints nullable="false" />
            </column>
            <column name="created_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
                <constraints nullable="false" />
            </column>
            <column name="created_by_id" type="BIGINT">
                <constraints foreignKeyName="first_table_created_by_id_fkey"
                             referencedTableSchemaName="ts"
                             referencedTableName="user"
                             referencedColumnNames="id"/>
            </column>
            <column name="modified_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
                <constraints nullable="false" />
            </column>
            <column name="modified_by_id" type="BIGINT">
                <constraints foreignKeyName="first_table_modified_by_id_fkey"
                             referencedTableSchemaName="ts"
                             referencedTableName="user"
                             referencedColumnNames="id"/>
            </column>
            <column name="status" type="VARCHAR(128)">
                <constraints nullable="false" />
            </column>
            <column name="invoice_id" type="VARCHAR(64)"></column>
        </createTable>
    </changeSet>

    <!-- *** SLOW QUERY -->
    <changeSet id="fix-index" author="author">
        <preConditions onFail="MARK_RAN">
            <not>
                <indexExists schemaName="ts" tableName="first_table" indexName="first_table_modified_by_id_idx"></indexExists>
            </not>
        </preConditions>
        <createIndex schemaName="ts" tableName="first_table" indexName="first_table_modified_by_id_idx">
            <column name="modified_by_id"></column>
        </createIndex>
    </changeSet>
</databaseChangeLog>
```
I don't really see much difference between the 42.7.5 and 42.7.6. I certainly don't see anything taking 21 seconds

DAve