Slow schema loading on UI for big databases

[vicmarbev]

Hi! We've noticed that the schema loading in the UI for tables with a lot of constraints, for example with Sentry's self hosted PostgreSQL database. The querythat seems to do the heavy lifting in this case is the one that gets the primary key constraints and for us takes around 10-12 seconds:

 SELECT
    c.table_schema,
    c.table_name,
    c.column_name
  FROM information_schema.table_constraints tc
  JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
  JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
    AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
  WHERE constraint_type = 'PRIMARY KEY'

Toying with it I think that we can omit the the information_schema.columns join as we have all the information that we need in the information_schema.constraint_column_usage table. This query for this table takes us around 1 second:

SELECT
    ccu.constraint_schema AS table_schema,
    ccu.table_name,
    ccu.column_name
FROM information_schema.constraint_column_usage AS ccu
JOIN information_schema.table_constraints AS tc 
    ON ccu.constraint_schema = tc.constraint_schema
    AND ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY';

What do you think? Would this work or could we miss something with this second query?

[feraio]

Hello @vicmarbev, thanks for opening this issue.
I'll take your case to the engineering team to test and evaluate it.

I let you know as soon as we have any updates.