PostgreSQL metadata sync can use an extremely slow execution plan for primary key detection

Describe the bug

When Metabase performs metadata sync for a PostgreSQL database, the query used to describe fields can select an extremely inefficient execution plan.

In our environment, the metadata query generated by Metabase for information_schema.columns together with primary key detection through information_schema.table_constraints and information_schema.key_column_usage behaves very differently between two PostgreSQL environments.

On TEST, the query completes in approximately 15 seconds.

On PROD, the same query can run for tens of minutes or longer without completing. During execution the PostgreSQL backend remains active with no wait event (wait_event_type = NULL, wait_event = NULL), so it is executing rather than waiting on a lock.

We compared the execution plans and found an important difference:

• TEST plan contains a Materialize node for the primary key subquery.
• PROD plan does not materialize this part of the query.
• enable_material = on in both environments.

As an experiment, we rewrote the same metadata query so that the primary key subquery is explicitly materialized:

WITH pk AS MATERIALIZED (
    SELECT
        tc.table_schema,
        tc.table_name,
        kc.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kc
      ON tc.constraint_name = kc.constraint_name
     AND tc.table_schema = kc.table_schema
     AND tc.table_name = kc.table_name
    WHERE tc.constraint_type = 'PRIMARY KEY'
)
SELECT ...
FROM information_schema.columns c
LEFT JOIN pk
  ON c.table_schema = pk.table_schema
 AND c.table_name = pk.table_name
 AND c.column_name = pk.column_name
...


On the same PROD database this version completes in approximately 10 seconds.

The original Metabase-generated query can run for a very long time, while the logically equivalent query with an explicitly materialized PK subquery completes quickly.

We also tested several PostgreSQL/JDBC/session-level settings, including:

prepareThreshold=0
plan_cache_mode=force_custom_plan
enable_nestloop=off
increased work_mem
autovacuum / autoanalyze
planner settings comparison between TEST and PROD

These experiments did not resolve the issue.

This appears to make the PostgreSQL metadata sync query highly sensitive to the execution plan selected by PostgreSQL.

A possible improvement would be to explicitly materialize the primary key subquery in the PostgreSQL describe-fields metadata query, or otherwise rewrite this part of the query so PostgreSQL does not repeatedly evaluate the expensive information_schema expression.

Metabase version: v0.63.1.2
PostgreSQL version: 17

[metabase_SELECT_SQL.sql](https://github.com/user-attachments/files/32291292/metabase_SELECT_SQL.sql)
[EXPLAIN (VERBOSE) PROD.csv](https://github.com/user-attachments/files/32291541/EXPLAIN.VERBOSE.PROD.csv)
[EXPLAIN (VERBOSE) TEST.csv](https://github.com/user-attachments/files/32291540/EXPLAIN.VERBOSE.TEST.csv)

### To Reproduce

1. Connect Metabase v0.63.1.2 to a PostgreSQL 17 database.
2. Run a metadata sync / field synchronization.
3. Observe the PostgreSQL query generated by Metabase for describing fields.
4. The query joins `information_schema.columns` with a primary key subquery based on:
   - `information_schema.table_constraints`
   - `information_schema.key_column_usage`
5. On our PROD database, this query remains active for many minutes and may continue for much longer without completing.
6. PostgreSQL shows the backend as `active` with:
   - `wait_event_type = NULL`
   - `wait_event = NULL`
7. Running an equivalent query manually with the primary key subquery explicitly materialized using `WITH ... AS MATERIALIZED` completes in approximately 10 seconds on the same PROD database.

This is highly dependent on the execution plan selected by PostgreSQL. On TEST, PostgreSQL adds a `Materialize` node automatically and the original query completes in approximately 15 seconds. On PROD, the corresponding plan does not contain that `Materialize` node.


### Expected behavior

PostgreSQL metadata synchronization should complete in a reasonable and predictable amount of time and should not depend so heavily on whether PostgreSQL happens to choose a plan that materializes the primary key subquery.

Ideally, the PostgreSQL metadata query should be structured so that the expensive primary-key lookup is evaluated once and reused, or otherwise avoid repeated execution of the expensive `information_schema` expression.

### Logs

The PostgreSQL backend executing the Metabase metadata query remains in the `active` state with no wait event:

- `state = active`
- `wait_event_type = NULL`
- `wait_event = NULL`

No lock wait was observed.

The Metabase-generated metadata query was captured from `pg_stat_activity`.

The same query was also tested manually with `EXPLAIN` / `EXPLAIN ANALYZE`. On PROD, the original query can run for a very long time, while a version using `WITH ... AS MATERIALIZED` for the primary-key subquery completes in approximately 10 seconds.

### Information about your Metabase installation

```JSON
v0.63.1.2, PostgreSQL 17

Diagnostic information can be provided later if required.

Severity

This issue blocks reliable metadata synchronization for our PostgreSQL production database. The metadata query can remain active for tens of minutes or longer, which prevents field synchronization from completing normally. Querying the database itself and normal BI usage are otherwise available, so this does not block all Metabase usage, but it blocks an important administrative function for this database.

Additional context

Additional observations:

• The same database schema contains approximately 5,771 columns returned by information_schema.columns.
• Running the primary-key lookup subquery separately completes in several seconds.
• Running the column-description part separately also completes quickly.
• The severe slowdown appears when these parts are combined in the metadata query generated by Metabase.
• On TEST, PostgreSQL chooses a plan containing Materialize.
• On PROD, PostgreSQL chooses a different plan without that materialization.
• Explicitly rewriting the PK subquery as WITH pk AS MATERIALIZED (...) makes the query complete in approximately 10 seconds on PROD.

We can provide the original generated SQL and both TEST/PROD execution plans if useful.