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?