Re: Ideas about a better API for postgres_fdw remote estimates

There are two different problems, which ultimately might converge.

1. If use_remote_estimates = false, more generally if querying costs from foreign server for costing paths is impractical, we want to use local estimates and try to come up with costs. For that purpose we keep some statistics locally and user is expected to refresh it periodically by running ANALYZE on the foreign table. This patch is about a. doing this efficiently without requiring to fetch every row from the foreign server b. through autovacuum automatically without user firing ANALYZE. I think this also answers your question about vacuum_rel() above.

2. How to efficiently extract costs from an EXPLAIN plan when use_remote_eestimates is true. That's the subject of some nearby thread. I think you are referring to that problem here. Hence your next point.

 

Using EXPLAIN to get costs from the foreign server isn't efficient. It increases planning time a lot; sometimes planning time exceeds execution time. If usage of foreign tables becomes more and more common, this isn't ideal. I think we should move towards a model in which the optimizer can decide whether a subtree involving a foreign server should be evaluated locally or on the foreign server without the help of foreign server. One way to do it (I am not saying that this is the only or the best way) is to estimate the cost of foreign query locally based on the information available locally about the foreign server and foreign table. This might mean that we have to get that information from the foreign server and cache it locally and use it several times, including the indexes on foreign table, values of various costs etc. Though this approach doesn't solve all of those problems it's one step forward + it makes the current scenario also efficient.

I agree that the patch needs some work though, esp the code dealing with serialization and deserialization of statistics.