DEV Community

Discussion on: Beware The Missing Foreign Key Index: A Postgres Performance Gotcha

Collapse
 
jbranchaud profile image
Josh Branchaud

Thanks, Ian! Unfortunately, the query plan does not expose that kind of information which is part of what made this particular issue so hard to track down.

As for finding all the tables that are dependent on a column in a given table, here is a query, for example, that finds all tables dependent on users.id. It takes advantage of Postgres's information_schema. Just replace the column name and table name in the where clause with whatever you are looking for.

select kcu.table_schema || '.' || kcu.table_name as foreign_table,
       '>-' as rel,
       rel_kcu.table_schema || '.' || rel_kcu.table_name as primary_table,
       kcu.ordinal_position as no,
       kcu.column_name as fk_column,
       '=' as join,
       rel_kcu.column_name as pk_column,
       kcu.constraint_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
join information_schema.key_column_usage rel_kcu
          on rco.unique_constraint_schema = rel_kcu.constraint_schema
          and rco.unique_constraint_name = rel_kcu.constraint_name
          and kcu.ordinal_position = rel_kcu.ordinal_position
where tco.constraint_type = 'FOREIGN KEY'
          and rel_kcu.column_name = 'id'
          and rel_kcu.table_name = 'users'
order by kcu.table_schema,
         kcu.table_name,
         kcu.ordinal_position;
Enter fullscreen mode Exit fullscreen mode

source

Collapse
 
michristofides profile image
Michael Christofides

Nice write up, thanks for sharing!

It’s a shame you didn’t use explain analyze, or explain (analyze, verbose), as it would have shown referential integrity triggers taking the majority of the time. Unfortunately it would have taken a full 30 minutes to execute, as it does run the query, so your instincts around using transactions would have been perfect for that too.

I’ve updated our explain glossary page to hopefully serve this case better too, thanks: pgmustard.com/docs/explain/trigger...

Thread Thread
 
lujop profile image
Joan Pujol

Are you sure that with analyze,verbose you see cost of checking integrity constraints?
I always use that options and I'm not able to see them.
Also I checked the link on your comment but it's not clear to me to what options the trigger information is related

Thread Thread
 
michristofides profile image
Michael Christofides

Hi Joan, here's an example: db-fiddle.com/f/6odBaA7hYaEvD3WGVU...

If you click Run on that, you should see a line starting with "Trigger RI_ConstraintTrigger". Hope that helps.

Thanks for feedback on the link too, I'll have a think about how to make that better.