Describe the problem
We're utilizing the recommended NPGSQL library for C#.
When a connection opens, the startup queries are run to get the database's metadata/structure.

This query is designed to only run once, on the very first connection, not for any additional connections.

Our community schema currently maintains about 22,000 tables and will be increasing to 80,000 as more communities are migrated. This initial query is now taking upwards of 2 minutes, running on an 8 core docker node (more specs can be sent as needed).

Because this query takes so long, it would hit the default connection Timeout parameter of 15 seconds, more requests/connections continue to make this request endlessly locking up the entire connection pool.

To temporarily resolve:

• Create and open a single connection with the Timeout set to the max of 1024
• Wait until this connection has fully opened (block all threads and any other requests)
• Resume operation

As our tables continue to grow, this initial connection lock is taking longer and longer. While the max Timeout allows for 1024 seconds, I fear the downtime scaling up to 5-10 minutes when we wish to restart.

Expected behavior
Reads/joins of the pg_catalog table should provide better optimization for large table counts. Supposedly this is a known and lower priority issue?

From a member in Slack:

We might be picking a bad join order because we have no stats on those virtual tables.

Additional data / screenshots

Environment:

• CockroachDB: CCL v22.1.2 (x86_64-pc-linux-gnu, built 2022/06/22 15:54:12, go1.17.11)
• Server OS: CentOS Linux release 7.9.2009
• Client app: NPGSQL v6.0.5, .Net 6.0

Jira issue: CRDB-17178

Epic CRDB-60809