The following SQL query which runs from an open source library I’m using finds the foreign key constraints within a schema/table. However the PG optimiser changes the way it optimises the query based upon the number of tables in the SCHEMA. Does anyone know if there is a way to force the use of the faster optimisation method? This query can be run on any recent version of Postgres.

SELECT tc.constraint_name, kcu.column_name, ccu.table_name, ccu.column_name, 0
FROM information_schema.table_constraints tc
         JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
         JOIN (SELECT ROW_NUMBER()
                      OVER ( PARTITION BY table_schema, table_name, constraint_name ORDER BY row_num ) AS ordinal_position,
                      table_schema,
                      table_name,
                      column_name,
                      constraint_name
               FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
                            table_schema,
                            table_name,
                            column_name,
                            constraint_name
                     FROM information_schema.constraint_column_usage) t) AS ccu
              ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema AND
                 ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
  AND tc.table_name = 'xxx';

I’ve used EXPLAIN (ANALYZE, BUFFERS) and basically the optimiser is just going off at a tangent, and the tipping point seems to be 95+ tables in the schema. I haven’t put the EXPLAIN in this thread as they are quite long, but anyone can try this on any Postgres database, it’s just querying the data model schema. I wonder if some trick exists to direct the use of one optimisation over the other without changing the SQL, as it’s part of an open source library so I’m not sure of the exact intent of the query?
Here is the tail of the EXPLAIN in both cases on the same laptop, same schema,, just different number of tables defined:

THE GOOD

->  Index Only Scan using pg_namespace_oid_index on pg_namespace nc_3  (cost=0.13..0.18 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (oid = c_3.connamespace)
Heap Fetches: 1
Buffers: shared hit=3
Planning:
  Buffers: shared hit=779 read=64
Planning Time: 9.951 ms
Execution Time: 7.677 ms

THE BAD

->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.29..0.46 rows=1 width=70) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: ((attrelid = r_2.oid) AND (attnum = ((information_schema._pg_expandarray(c_2.conkey))).x))
"        Filter: ((NOT attisdropped) AND (pg_has_role(r_2.relowner, 'USAGE'::text) OR has_column_privilege(r_2.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))"
Buffers: shared hit=3
Planning:
  Buffers: shared hit=118
Planning Time: 10.022 ms
Execution Time: 1254.787 ms

I’ve tried reindex database and upping the cache sizes but nothing brings the fast optimisation back.
Thanks in advance