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.