RE: JDBC Meta-data performance for Foreign Keys

From: Sehrope Sarkuni <sehrope(at)jackdb(dot)com>
Sent: Thursday, September 30, 2021 11:14
To: ldh(at)laurent-hasson(dot)com
Cc: pgsql-jdbc(at)lists(dot)postgresql(dot)org
Subject: Re: JDBC Meta-data performance for Foreign Keys

On Fri, Sep 10, 2021 at 1:53 PM mailto:ldh(at)laurent-hasson(dot)com <mailto: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/

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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.