Thread: JDBC Meta-data performance for Foreign Keys
Hello all,
I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreign keys for all tables. These operations are very expensive. I am using the following code:
ResultSet RS = meta.getImportedKeys(null, schemaName, tableName);
ResultSet RS = meta.getExportedKeys(null, schemaName, tableName);
I have instrumented my code to return the times spent in all the various things I am getting:
Schemas : 23 in 33.00ms or 0.3%
Tables : 311 in 3.00ms or 0.0%
TableColumns: 6375 in 1,651.00ms or 13.2%
PK : 307 in 960.00ms or 7.6%
FK-Out : 381 in 4,451.00ms or 35.5%
FK-In : 378 in 4,067.00ms or 32.4%
Indices : 824 in 1,385.00ms or 11.0%
Views : 362 in 18.00ms or 0.1%
I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC drivers V42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected? Is there a way that I could make this faster?
Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experience any speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any idea here?
Thank you,
Laurent.
Hello all,
I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreign keys for all tables. These operations are very expensive. I am using the following code:
ResultSet RS = meta.getImportedKeys(null, schemaName, tableName);
ResultSet RS = meta.getExportedKeys(null, schemaName, tableName);
I have instrumented my code to return the times spent in all the various things I am getting:
Schemas : 23 in 33.00ms or 0.3%
Tables : 311 in 3.00ms or 0.0%
TableColumns: 6375 in 1,651.00ms or 13.2%
PK : 307 in 960.00ms or 7.6%
FK-Out : 381 in 4,451.00ms or 35.5%
FK-In : 378 in 4,067.00ms or 32.4%
Indices : 824 in 1,385.00ms or 11.0%
Views : 362 in 18.00ms or 0.1%
I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC drivers V42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected? Is there a way that I could make this faster?
Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experience any speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any idea here?
Thank you,
Laurent.
From: ldh@laurent-hasson.com <ldh@laurent-hasson.com> Sent: Friday, September 10, 2021 13:53 To: pgsql-jdbc@lists.postgresql.org Subject: RE: JDBC Meta-data performance for Foreign Keys Hello all, I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreignkeys for all tables. These operations are very expensive. I am using the following code: ResultSet RS = meta.getImportedKeys(null, schemaName, tableName); ResultSet RS = meta.getExportedKeys(null, schemaName, tableName); I have instrumented my code to return the times spent in all the various things I am getting: Schemas : 23 in 33.00ms or 0.3% Tables : 311 in 3.00ms or 0.0% TableColumns: 6375 in 1,651.00ms or 13.2% PK : 307 in 960.00ms or 7.6% FK-Out : 381 in 4,451.00ms or 35.5% FK-In : 378 in 4,067.00ms or 32.4% Indices : 824 in 1,385.00ms or 11.0% Views : 362 in 18.00ms or 0.1% I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC driversV42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected?Is there a way that I could make this faster? Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experienceany speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any ideahere? Thank you, Laurent. --------------------------------------------------- Hello all, Any possible help around this particular issue? Thank you, Laurent.
Hello,
Would appreciate any info about this if possible!
Thank you,
Laurent
From: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Sent: Friday, September 10, 2021 13:53
To: pgsql-jdbc@lists.postgresql.org
Subject: RE: JDBC Meta-data performance for Foreign Keys
Hello all,
I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreign keys for all tables. These operations are very expensive. I am using the following code:
ResultSet RS = meta.getImportedKeys(null, schemaName, tableName);
ResultSet RS = meta.getExportedKeys(null, schemaName, tableName);
I have instrumented my code to return the times spent in all the various things I am getting:
Schemas : 23 in 33.00ms or 0.3%
Tables : 311 in 3.00ms or 0.0%
TableColumns: 6375 in 1,651.00ms or 13.2%
PK : 307 in 960.00ms or 7.6%
FK-Out : 381 in 4,451.00ms or 35.5%
FK-In : 378 in 4,067.00ms or 32.4%
Indices : 824 in 1,385.00ms or 11.0%
Views : 362 in 18.00ms or 0.1%
I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC drivers V42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected? Is there a way that I could make this faster?
Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experience any speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any idea here?
Thank you,
Laurent.
I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreign keys for all tables. These operations are very expensive. I am using the following code:
ResultSet RS = meta.getImportedKeys(null, schemaName, tableName);
ResultSet RS = meta.getExportedKeys(null, schemaName, tableName);
I have instrumented my code to return the times spent in all the various things I am getting:
Schemas : 23 in 33.00ms or 0.3%
Tables : 311 in 3.00ms or 0.0%
TableColumns: 6375 in 1,651.00ms or 13.2%
PK : 307 in 960.00ms or 7.6%
FK-Out : 381 in 4,451.00ms or 35.5%
FK-In : 378 in 4,067.00ms or 32.4%
Indices : 824 in 1,385.00ms or 11.0%
Views : 362 in 18.00ms or 0.1%
I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC drivers V42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected? Is there a way that I could make this faster?
Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experience any speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any idea here?
From: Sehrope Sarkuni <sehrope@jackdb.com> Sent: Thursday, September 30, 2021 11:14 To: ldh@laurent-hasson.com Cc: pgsql-jdbc@lists.postgresql.org Subject: Re: JDBC Meta-data performance for Foreign Keys On Fri, Sep 10, 2021 at 1:53 PM mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote: I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreignkeys for all tables. These operations are very expensive. I am using the following code: ResultSet RS = meta.getImportedKeys(null, schemaName, tableName); ResultSet RS = meta.getExportedKeys(null, schemaName, tableName); I have instrumented my code to return the times spent in all the various things I am getting: Schemas : 23 in 33.00ms or 0.3% Tables : 311 in 3.00ms or 0.0% TableColumns: 6375 in 1,651.00ms or 13.2% PK : 307 in 960.00ms or 7.6% FK-Out : 381 in 4,451.00ms or 35.5% FK-In : 378 in 4,067.00ms or 32.4% Indices : 824 in 1,385.00ms or 11.0% Views : 362 in 18.00ms or 0.1% I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC driversV42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected?Is there a way that I could make this faster? If you are getting the data for hundreds of tables, rather than invoking those methods one-by-one for each table, you caninvoke them with a null value for the schemaName and/or tableName to get results for many tables in step. You'll needto filter the results yourself to properly associate the results with the right table, but it should be doable usingthe fields in the result set. This batch approach should speed things up as you'll have a single round trip rather than hundreds (one per table). Both of those methods calls the same internal method that executes a single SQL command: https://github.com/pgjdbc/pgjdbc/blob/3a2bbd77969903f8a4ce721d45905c72bd1688d6/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L2226-L2320 Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experienceany speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any ideahere? If you're only interested in a subset of the fields and are willing to code your application specifically to Postgres internals(rather than the generic JDBC API), you can customize that SQL and execute your own version directly to get onlythe fields you need. Regards, -- Sehrope Sarkuni Founder & CEO | JackDB, Inc. | https://www.jackdb.com/ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Thank you Sehrope, that was a great idea! Lots of code refactoring on our end, but it was worth it as the results are quite good with a ~70% improvement! The only gotcha is what I tried to also optimize the calls for indices, I was surprised that the jdbc method DatabaseMetaData.getIndexInfo()doesn't take a table pattern like other "getXXXInfo" methods. For now we have decent performance,so dropping to a custom query to optimize indices is something we can do later. pre-optimization ========================================== Schemas: 23 in 42.00ms or 0.2% Tables: 312 in 4.00ms or 0.0% TableColumns: 6293 in 2,478.00ms or 12.6% PK: 308 in 1,381.00ms or 7.0% FK-Out: 381 in 7,186.00ms or 36.5% FK-In: 378 in 6,656.00ms or 33.8% Indices: 823 in 1,915.00ms or 9.7% Views: 362 in 22.00ms or 0.1% ------------------------------------------ Total: 19,667.00ms post-optimization ========================================== Schemas: 23 in 38.00ms or 0.7% Tables: 308 in 13.00ms or 0.2% TableColumns: 17917 in 643.00ms or 11.1% PK: 308 in 1,293.00ms or 22.3% FK-Out: 381 in 1,215.00ms or 20.9% FK-In: 378 in 697.00ms or 12.0% Indices: 823 in 1,907.00ms or 32.8% Views: 362 in 113.00ms or 1.9% ---------------------------------------------------------------------------- Total: 5,809.00ms Thank you, Laurent.
On 02-10-2021 08:29, ldh@laurent-hasson.com wrote: > Lots of code refactoring on our end, but it was worth it as the results are quite good with a ~70% improvement! > > The only gotcha is what I tried to also optimize the calls for indices, I was surprised that the jdbc method DatabaseMetaData.getIndexInfo()doesn't take a table pattern like other "getXXXInfo" methods. For now we have decent performance,so dropping to a custom query to optimize indices is something we can do later. > > > pre-optimization > ========================================== > [..] > Views: 362 in 22.00ms or 0.1% > ------------------------------------------ > Total: 19,667.00ms > > > post-optimization > ========================================== > [..] > Views: 362 in 113.00ms or 1.9% > ---------------------------------------------------------------------------- > Total: 5,809.00ms I notice that you seem to be requesting views separately. You should be able to do that in a single call to DatabaseMetaData.getTables together with retrieving the tables. Mark -- Mark Rotteveel
> -----Original Message----- > From: Mark Rotteveel <mark@lawinegevaar.nl> > Sent: Saturday, October 2, 2021 04:57 > To: pgsql-jdbc@lists.postgresql.org > Subject: Re: JDBC Meta-data performance for Foreign Keys > > On 02-10-2021 08:29, ldh@laurent-hasson.com wrote: > > Lots of code refactoring on our end, but it was worth it as the results > are quite good with a ~70% improvement! > > > > The only gotcha is what I tried to also optimize the calls for indices, I > was surprised that the jdbc method DatabaseMetaData.getIndexInfo() > doesn't take a table pattern like other "getXXXInfo" methods. For now > we have decent performance, so dropping to a custom query to optimize > indices is something we can do later. > > > > > > pre-optimization > > ========================================== > > [..] > > Views: 362 in 22.00ms or 0.1% > > ------------------------------------------ > > Total: 19,667.00ms > > > > > > post-optimization > > ========================================== > > [..] > > Views: 362 in 113.00ms or 1.9% > > ---------------------------------------------------------------------------- > > Total: 5,809.00ms > > I notice that you seem to be requesting views separately. You should be > able to do that in a single call to DatabaseMetaData.getTables together > with retrieving the tables. > > Mark > -- > Mark Rotteveel > It's the way we do the logging for processing... But in any case, we are talking a few milliseconds here so not somethingI was paying attention to specifically. Thank you, Laurent.