Re: JDBC Meta-data performance for Foreign Keys

On Fri, Sep 10, 2021 at 1:53 PM ldh(at)laurent-hasson(dot)com <
ldh(at)laurent-hasson(dot)com> wrote:

> 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?
>

If you are getting the data for hundreds of tables, rather than invoking
those methods one-by-one for each table, you can invoke them with a null
value for the schemaName and/or tableName to get results for many tables in
step. You'll need to filter the results yourself to properly associate the
results with the right table, but it should be doable using the 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 experience any speedup, as if
> everything was getting serialized either at the JDBC driver level, or in
> the database. Any idea here?
>

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 only the fields you need.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/