Simon Riggs <simon@2ndquadrant.com> writes: > So a simple change is to make RelationGetFKeyList() only retrieve FKs with > nKeys>1. Rename to RelationGetMultiColumnFKeyList(). That greatly reduces > the scope for increased planning time.
FWIW, I don't particularly agree with that. It makes the relcache's fkey storage extremely specific to this one use-case, a decision I expect we'd regret later.
Hmm, clearly I thought that earlier also; that earlier thinking may be influencing you. My commits had the concept of generic FK info and then a specific optimization. So the main part of the planning problem was caused by stored info that would never be used, in 9.6.
What changes my mind here is 1) point in dev cycle, 2) the point that the list of FKs doesn't take into account whether the constraints are deferrable, deferred or immediate and whether they are valid/invalid. ISTM likely that we would care about those things in the future if we believe that info is generic.
But then each new usage of the info will have the same planning time problem to consider if they choose to extend the amount of info they hold.
Rejecting an optimization in 9.6 because it might be undone by later changes is surely a problem for those later changes to resolve.
And the planner needs to filter the fkey list anyway, because it only wants fkeys that link to tables that are also in the current query. Thus, my recommendation was that we should allow RelationGetFKeyList to return a pointer directly to the cached info list and require the planner to immediately copy (only) the entries that it needs for the current query.
Another point here is that I'm now unconvinced that restricting the logic to consider only multi-column fkeys is really what we want. It looks to me like the code can also improve estimates in the case where there are multiple single-column FKs linking to the same target relation. That might not be too common for two-table queries, but I bet it happens a lot in three-or-more-table queries.
Is it realistic that we consider that at this point? Certainly not for myself, at least.
--
Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services