I wonder if PG will ever implement plan caching like MSSQL so that the speed of the optimizer is less of a concern and it can take more time finding better plans rather than replanning on every execution of the same statement.
Postgres used to have plan caching inside the same session, and that was so disastrous that it was limited severely by default.
Plan caching is very much a two-edged sword; cache too aggressively, and the situation will be different between the runs. Cache too little, and your hit rates are useless.
Not sure how that makes sense, if the stats change significantly then caches would be evicted during the gathering of statistics.
I believe popular connection poolers and clients attempt to do plan caching through prepared statements and keeping the connection open.
My understanding its not easy to do in PG since connections are process based instead of thread based and the query plans are not serializable between processes, so they cannot be shared between connections.
MSSQL has been doing statement plan caching for at least 20 years and it did stored procedure plan caching before that.
