JDBC getIndexInfo for large table is very slow

[Silvio]

I have a largisch database (~6G) which I use in H2 2.1.214 embedded mode on an NVME SSD. I use the JDBC call

rsIdx = con.getMetaData.getIndexInfo(catalog,schema,table,false,false)

to get the indexes for a single table (which is by far the largest in the whole database) that contains about 3.6 million rows. The call takes about 14 seconds to return the index es/segments for the table. On a not-so-fast older SSD the same call takes ~30 seconds.

Is there a reason that this takes so long? Is there a more efficient way to determine which indexes are present on a table?

Thanks,

Silvio

[Noel Grandin]

How many tables do you have, that should return in milliseconds.

You could try rinning the built in profiler (see the docs) and see what it is doing.

You could also directly query the metadata table INFORMATION_SCHEMA.INDEXES

[Silvio]

I read up on the method and now realize the call returns a number of statistics (like CARDINALITY) that is based on the rows in the index. That explains the slowness. The problem is that I only need to know which indexes there are on each table and do not care about the statistics.

The tables are created dynamically based on meta data extracted from application user definitions (surveys). The application offers an admin interface that displays existing indexes and allows users to add/remove indexes if their use of the datasets (reporting dashboards) requires it.

[Silvio]

Thanks Noel,

Also see my other remark. I will look into that table. Actually, we did some stuff directly on the INFORMATION_SCHEMA tables before but the H2 versions above 200 had some modified layout/content in there so we switched to the JDBC call for this one.

[Silvio]

BTW: there are usually about 30 tables. Some approaching 100K rows, the big one being the only that goes into the millions.

[Evgenij Ryazanov]

Hello!

You need to pass true as the last argument (approximate). It allows to return a fast approximation instead of exact number of rows in the index.

[Silvio]

Right! Thanks Evgenij, I will change that immediately.

Thanks again guys.