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
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.