pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: davecramer (@davecramer) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #3511: Performance Regression in JDBC Driver 42.7.5 - getCrossReference
Date: Wed, 07 May 2025 18:32:19 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

@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

view thread (24+ messages)  latest in thread

reply

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: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #3511: Performance Regression in JDBC Driver 42.7.5 - getCrossReference
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox