Re: hashjoins, index loops to retrieve pk/ux constrains in pg12

I believe that this is a planning problem with the number of tables/joins involved in the query you have written. If you take a look at the definition of the views in information_schema that you are using and read about from_collapse_limit/join_collapse_limit, you may see that this is a bit painful for the planner. It might be cumbersome to use the actual system tables underneath, but that would certainly lead to much better performance. Otherwise, I would look at perhaps putting the view that has a WHERE condition on it as the FROM to encourage the planner to perhaps filter that set first and join the other tables after. If that didn't help, I might even use a materialized CTE to force the issue.

Hopefully a real expert will chime in with a better explanation of the challenges or preferred solution.