RE: JDBC Meta-data performance for Foreign Keys

Hello,

Would appreciate any info about this if possible!

Thank you,
Laurent

From: ldh(at)laurent-hasson(dot)com <ldh(at)laurent-hasson(dot)com>
Sent: Friday, September 10, 2021 13:53
To: pgsql-jdbc(at)lists(dot)postgresql(dot)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.