I'm a developer and consultant focused primarily on the web, specializing in React, Ruby on Rails, and PostgreSQL. Newsletter: https://crafty-builder-6996.ck.page/e169c61186
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.
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.
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
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'sinformation_schema
. Just replace the column name and table name in thewhere
clause with whatever you are looking for.source
Nice write up, thanks for sharing!
It’s a shame you didn’t use
explain analyze
, orexplain (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...
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
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.