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.