On 07/07/2018 20:07, Jeff Janes wrote: > One case that your patch doesn't improve (neither does my posted one) is > check constraints. To fix that, pg_get_constraintdef_worker would also > need to grow a cache as well. I don't know how often people put check > constraints on most of the columns of a table. Some people like their > NOT NULL constraints to be named, not implicit. > > But from the bigger picture of making pg_upgrade faster, a major issue > is that while pg_dump -s gets faster for the column default case, the > restore of that dump is still slow (again, my posted patch also doesn't > fix that). In that case it is deparse_context_for called from > StoreAttrDefault which is slow.
Any thoughts on how to proceed here? It seems there is more work to do to cover all the issues with dumping and restoring tables with many columns. Since the original report was in the context of pg_upgrade, we should surely address at least the pg_restore slowness.
I'll working on solving the problem using a hash table at the lowest level (making column names unique), for a future commit fest. That should drop it from N^3 to N^2, which since N can't go above 1600 should be good enough.
So we can set this to rejected, as that will be an entirely different approach.
Your caching patch might be worthwhile on its own, though.