Bad Performance TPostgreSQLSchemaRetriever.GetForeignKeys on Postgres V17

BIZ TMS Aurelius
1

We would like to upgrade our postgres to v17.

Using a migrated pg V16 Database, your SQL

select
  TC.constraint_name,
  TC.table_name as FK_TABLE_NAME,
  TC.table_schema as FK_TABLE_SCHEMA,
  KP.table_name as PK_TABLE_NAME,
  KP.table_schema as PK_TABLE_SCHEMA,
  KC.column_name as FK_COLUMN_NAME,
  KP.column_name as PK_COLUMN_NAME
from
  information_schema.table_constraints TC,
  information_schema.referential_constraints RC,
  information_schema.key_column_usage KC,
  information_schema.key_column_usage KP
where
  TC.constraint_name = RC.constraint_name
  and TC.constraint_schema = RC.constraint_schema
  and RC.constraint_schema = KC.constraint_schema
  and RC.constraint_name = KC.constraint_name
  and RC.unique_constraint_name = KP.constraint_name
  and RC.unique_constraint_schema = KP.constraint_schema
  and KC.table_name = TC.table_name
  and KC.table_schema = TC.table_schema
  and KC.position_in_unique_constraint = KP.ordinal_position
  and TC.TABLE_SCHEMA not in ('pg_catalog', 'information_schema')
order by
  TC.table_schema,
  TC.table_name,
  TC.constraint_name,
  KC.ordinal_position

will not finish at all.
It needs about 0,5 secs in postgres v16.
In v17 I have to wait more than 5 min for first 200 entries.

rewriting this to ansi-joins we would get 200 entries within 20 secs:

select
  TC.constraint_name,
  TC.table_name as FK_TABLE_NAME,
  TC.table_schema as FK_TABLE_SCHEMA,
  KP.table_name as PK_TABLE_NAME,
  KP.table_schema as PK_TABLE_SCHEMA,
  KC.column_name as FK_COLUMN_NAME,
  KP.column_name as PK_COLUMN_NAME
from
  information_schema.table_constraints TC
join information_schema.referential_constraints RC 
  on
  TC.constraint_name = RC.constraint_name
  and TC.constraint_schema = RC.constraint_schema
join information_schema.key_column_usage KC
  on
  RC.constraint_schema = KC.constraint_schema
  and RC.constraint_name = KC.constraint_name
  and KC.table_name = TC.table_name
  and KC.table_schema = TC.table_schema
join information_schema.key_column_usage KP
  on
  RC.unique_constraint_name = KP.constraint_name
  and RC.unique_constraint_schema = KP.constraint_schema
  and KC.position_in_unique_constraint = KP.ordinal_position
where
  TC.TABLE_SCHEMA not in ('pg_catalog', 'information_schema')
order by
  TC.table_schema,
  TC.table_name,
  TC.constraint_name,
  KC.ordinal_position

I'm just checking your SQL with dbeaver.

Maybe there is a better way to fetch those data?

Edit:
This also depends on size.
for small databases it works fine, but we got at least some databases with more than 550 foreign keys.

2

Sigh, looks like a PostgreSQL issue, isn't it? How could they made the same SQL 600 times slower from one version to another?

Changing such SQL is not that trivial as it affects many versions and years and years of a code that works just fine. Who knows if changing the SQL won't make it slower in PG 16 and older versions?

3

It looks like being an postgres issue, that's wright, but I tried fetching those data from postgres-tables directly without using information_schema views.

Following SQL just needs 0,005 sec on pg17 and 0,001 sec on pg16.
ValidateDatabase and thread-safe initialization of our mapping explorer instances would be amazing fast.

'SELECT '+
'  con.conname                    AS constraint_name, '+
'  nspfk.nspname                  AS FK_TABLE_SCHEMA, '+
'  relfk.relname                  AS FK_TABLE_NAME, '+
'  attfk.attname                  AS FK_COLUMN_NAME, '+
'  nsppk.nspname                  AS PK_TABLE_SCHEMA, '+
'  relpk.relname                  AS PK_TABLE_NAME, '+
'  attpk.attname                  AS PK_COLUMN_NAME '+
'FROM pg_constraint con '+
//'  -- only Foreign‐Key‐Constraints '+
'  JOIN pg_class relfk           ON relfk.oid = con.conrelid '+
'  JOIN pg_namespace nspfk       ON nspfk.oid = relfk.relnamespace '+
'  JOIN pg_class relpk           ON relpk.oid = con.confrelid '+
'  JOIN pg_namespace nsppk       ON nsppk.oid = relpk.relnamespace '+
//'  -- conkey / confkey are int2vector: unnest first ... '+
'  JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS fkcols(colnum, ord) ON TRUE '+
'  JOIN LATERAL unnest(con.confkey) WITH ORDINALITY AS pkcols(colnum, ord) ON pkcols.ord = fkcols.ord '+
//'  -- ... real Attributes '+
'  JOIN pg_attribute attfk       ON attfk.attrelid = relfk.oid AND attfk.attnum = fkcols.colnum '+
'  JOIN pg_attribute attpk       ON attpk.attrelid = relpk.oid AND attpk.attnum = pkcols.colnum '+
'WHERE con.contype = ''f'' '+//          -- f = FOREIGN KEY '+
'  AND nspfk.nspname NOT IN (''pg_catalog'',''information_schema'') '+
'ORDER BY '+
'  nspfk.nspname, '+
'  relfk.relname, '+
'  con.conname, '+
'  fkcols.ord;'